Month: November 2016

SAS: Proc Boxplot and Proc Sgplot

It is easier to use proc sgplot than proc boxplot to compare distibution by classification variable.  “Drive Train” and “Type” are both categorical variables.

proc sgplot data=sashelp.cars;
title "Price distribution by Drive Train and Type";
vbox invoice / category =type group = drivetrain;
run;
  • side by side comparison
  • group became legend
  • applied legend color by the group
  • inset statement for sgplot doesn’t have statistics output (n/min/max/mean/stddev)

SGPlot22

proc sort data=sashelp.cars out=cars;
by drivetrain type;
run;
proc boxplot data=cars;
title "Price distribution by Drive Train and Type";
plot invoice*type;
by DriveTrain;
inset min mean max stddev/ header = "Overall Statistics";
insetgroup min max / header = "Cheap and Expensive by Type";
run;
  • need to sort the data first according to by statement and plot categorical variable;
  • plot in light blue; want other color, need extra code
  • not able to show 2 categorical variable plot side by side;
  • use by statement use produce plot separately.
  • inset and insetgroup are nice to have to produce stats as part of the plot.
    • inset: data, min, max, mean, nmax, nmin, dobs, stddev;
    • insetgroup: max, mean, min, n, nhigh, nlow, nout, q1, q2, q3, range, stddev;

Boxplot22

Boxplot24

VBA: Tricks in Access VBA and VBscripting(1)

  1.  Naming of the procedures: Don’t name the procedure name the same as the module names.

module name

 

modulename

procedure name

procedurename

The following VBscripting code is trying to run a sub within Access. The sub is in a module called “RunQueries”.  The sub was named “RunQueries” initially because it is the only procedure in the module. The Run method in the VBcripting couldn’t find the sub in the Access because it has the same name as the module.  I changed the sub name to “OpenQueries” and the VPscript works fine now.

Dim objaccess
Set objaccess =CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "D:\accessdb.mdb", False
objAccess.Run ("OpenQueries")
ObjAccess.Quit
set objAccess = Nothing

2. Name of the Object: If the object has event procedure attached to it, the name of the object needs to be consistent with the object name reference in the procedure name.

Object Name

objectname

Procedure Name

objectprocedurename

When you change object name in the Property window, the corresponding procedure name in the code window doesn’t update automatically and the event procedure following the old procedure name will not run until you manually change the procedure name prefix to match the new object name.

3. Compact Access database in VPscript

objAccess.Application.SetOption "Auto compact", True

SAS: Use Filename to Create data set of hierarchical folder structure and file list

FILENAME contents pipe 'TREE "x:\project" /F /A' LRECL = 2000;
DATA project;
infile contents TRUNCOVER;
INPUT content_entry $char2000.;
content_entry = left(compress(content_entry, "|"));
run;

TREE: disply each directory within the listed directory. /F: display the names of the files within each directory.

proc sql noprint;
select count (*) into : file_exist
from project
where content_entry = "table1.sas7bdat";
quit;
%put file_exist = &file_exist.;