Month: March 2018

SAS: Configuration

SAS configuration file sasv9.cfg can be edited to change the SAS system options at SAS initialization. Certain system options can only be configured through the .cfg file, for example, -memsize and -altlog. -memsize defines the ram size used for SAS session; -altlog defines the file directory for copy of the sas log. In the configuration file, the syntax for system option is ‘-option’ which is different from when they are used in sas programs.

The Default Configuration file
!SASROOT = C:\Program Files\SASHome\x86\SASFoundation\9.3
Default folder (where all plots and results are saved) = C:\Program Files\SASHome\x86\SASFoundation\9.3

Sometimes there are two sasv9.cfg files in separate SAS folders, but one file will be pointing to the other sasv9.cfg which has the configuration setup.
Configuration location = C:\Program Files\SASHome\x86\SASFoundation\9.3\sasv9.cfg
-config “C:\Program Files\SASHome\x86\SASFoundation\9.3\nls\en\sasv9.cfg”
Default configuration location = C:\Program Files\SASHome\x86\SASFoundation\9.3\nls\en\sasv9.cfg

Example 1: change memsize

-MEMSIZE 4G

Example 2: save a copy of the SAS log from the session to user-defined directory

-ALTLOG  "C:\users\ussername\documents\log\%Y-%m-%d_%H-%M.saslog"

SAS: Output Table to Excel Using Tagsets.excelxp

Frequently we need to output SAS results to Excel.  With tagsets.excelxp, we will have the flexibility to create multiple sheets; define worksheet name(s) and column width; contrl titles and footnotes on each sheet.

Here is the basic tageset.excel for the Excel workbook.

ods listing close;
ods tagsets.excelxp path = "" file = "" style =
options (embedded_titles='' embedded_footnotes='' orientation='' sheet_name ='' pages_fitwidth='' pages_fitheight='' autofit_height=''
width_fudge="" absolute_column_width= "");
ods tagsets.excelxp close;

If the individual worksheet has different setting, put the following  statement before the corresponding SAS procedure that generates the content for the worksheet.

ods tagsets.excelxp options (sheet_name='RespHome');

Use sheet_name and sheet_interval to generate multiple sheets by groups defined in the procedure in the same Excel workbook.

... sheet_name='#byval1' sheet_interval="bygroup" ...

sample code:

ods listing close;
ods tagsets.excelxp path = "C:\SAS\" file = "sum.xls" style = journal
options (embedded_titles='yes' embedded_footnotes='yes' orientation='portrait' pages_fitwidth='1' pages_fitheight='1' autofit_height='yes'
width_fudge=".0625" absolute_column_width= "100");
title1 'Title1';
title2 "Title2";
footnote "Prepared by First Last @dept on %sysfunc(date(),worddate.)";
ods tagsets.excelxp options (sheet_name='homeresp');
proc tabulate data=count;
class home /order= formatted ;
class reponsible;
var count;
table (home="" all="Total"), (responsible="Responsible Faculty" all="Total")*(count="")*(sum="" )*f=10.3/ box = "Home Faculty" misstext = '0.000';
run;
ods tagsets.excelxp options (sheet_name='RespHome');
proc tabulate data=count;
class responsible /order= formatted ;
class home ;
var count;
table (responsible="" all="Total"), (home="Home Faculty" all="Total")*(count="")*(sum="" )*f=10.3/ box = "Resonsible Faculty" misstext = '0.000';
run;
ods tagsets.excelxp close;

SAS Proc Tabulate: Order Class Variable

The following block of code provides a cross-tabulation by student’s home faculty and responsible faculty who students took course from.  The special treatment in the code is the two class statements used for setting order for the home faculty class variable based on format of the home faculty.  The second class statement where the responsible class variable is defined doesn’t have the “/order” option, therefore the order for responsible variable will be based on internal value.

proc tabulate data=count;
class home /order= formatted ;
class responsible;
var ffte;
table (home="Home Faculty" all), (responsible="Responsible Faculty" all)*(count="")*(sum="");
run;

SAS: Data Set Compilation through Remote Submit and Download

Sometime it is better to submit the block of codes to the server for execution when the data sets are large and multiple years of data needed to be  compiled.  The remote calculation can also free up the calculation capacity on your own PC, so you can work on something else while waiting for the server.

The following codes are used to:

  • loop through 5 reporting periods each year;
  • retrieve specific variables that meet the conditions;
  • compile data set for each year;
  • stack data sets for multiple years and generate final data set with all years records for download;
  • delete data set for each year in the workspace on the server

The caveats for remote submit are:

  • codes need to be thoroughly validated locally before submit to the server
  • use “nodetails” and “nolist” to omit output report

To remote submit the code, you first select the code block,  then right click and then select “Remote Submit…”.

%let varlist1 = var1 var2 var3 var4 var5 var6 var7 ;
proc datasets library = work nodetails nolist;
delete opsis;
run;
%macro opsis (strtyear, stopyear);
%do yr= &strtyear %to &stopyear;
%put &yr.;
%let nextyr = %eval(&yr.+1);
%let charyr = %substr(&yr.,3,2);
%let charnextyr = %substr(&nextyr.,3,2);
%put &charyr.;
DATA opsis_&charyr.;
SET opsis.uecJun&charyr. (keep=&varlist1.)
opsis.uecJul&charyr. (keep=&varlist1.)
opsis.uecNov&charyr. (keep=&varlist1.)
opsis.uecFeb&charnextyr. (keep=&varlist1.)
opsis.uecMar&charnextyr. (keep=&varlist1.);
where var1 ne 'XX' and var2 ne '' and var3= 'XXX';
proc append base=opsis data =opsis_&charyr.;
run;
%end;
%mend opsis;
%opsis (2009, 2017);
proc download data = opsis out=opsis;run;
proc datasets library=work nodetails;
delete opsis_09 opsis_10-opsis_17;
run;

SAS: Proc Freq for all Character Variables

Before processing the large data set, it is a good practice to check the levels of the categorical varaible and make sure records are assigned to the right category and missing categories are treated.

Use _character_ in the freqency procedure to apply the procedure to all character variables.  Be careful not to include any character variables that are numeric in nature, eg id variable in the data set.  Using “nlevels” option to provide a summary table of number of the levels, missing levels, non-missing levels for each categorical variable.  Then check the frequency table for the specific categorical variable for the values of the levels.

proc freq data= table1 nlevels;
tables _character_ / nocum nopercent out=freqcnt;
run;
Number of Variable Levels
Variable Label Levels Missing Levels Nonmissing Levels
gender Gender Code 4 0 4
Var2 label2 12 1 11
Gender Code
gender Frequency
F 495320
M 402399
U 3475
{NUL} 1

SAS: Sample Data Sets

SAShelp and SASuser data library contain many data sets for the base SAS usages.  But for components other than base SAS, such as SAS/EST and SAS/STAT, the examples used in the user manual are not directly in the SAShelp and SASuser libraries.  Need to use the “Help” menu in SAS to access the datasets that are referred in the these user manuals.

  1. Help Menu
  2. SAS Help and Documentation
  3. Contents Tab
  4. Learning to Use SAS
  5. Sample SAS Programs
  6. Relevant Compontent (eg: SAS/STAT, SAS ETS)
  7. Samples

VBScript: Open and Process Excel Files in a Folder and Save in another Folder

In cmd, run “cscript test.vbs” under the folder where test.vbs is saved.

Contents of test.vbs

Set objFSO = CreateObject("Scripting.FileSystemObject")
sFolder = "X:\Work\test1\"
oFolder = "X:\Work\test1\result\"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
For Each objFile In objFSO.GetFolder(sFolder).Files
Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name)
objWorkbook.sheets("delete").Select
objWorkbook.sheets("delete").Delete
objWorkbook.SaveAs oFolder & objFile.Name
objWorkbook.Close
objExcel.Quit
Set objWorkbook = Nothing
Next
Set objFSO = Nothing
Set objExcel = Nothing;