Category: SAS

SAS Proc Upload and Download

SAS server processes dataset at a much faster rate than the local desktop.  To send the datasets from the WORK library of the desktop to the WORK library of the server,  and get the processed datasets from WORK library of the server to the WORK library of the desktop, we need to use PROC UPLOAD and PROC DOWNLOAD.

proc update data =localdata out =serverdata; run;
…code…
proc download data=serverdata out =localdata; run;

SAS Array example

In the data set, the student courses have 20 fields and the student grades have 20 fields and the courses and grades are one to one match.  The following codes can be used to get the course name from course field and grade from the grade field.

array crs {20} crs1-crs20;
array course {20} course1-course20 ;
array grade {20} grade1-grade20;
array gr {20} gr1-gr20 ;
do i = 1 to 20;
if crs{i} ne ” then course{i} = substr(crs{i},9,10);
if grade{i} ne ” then gr{i} =scan(grade{i},5,’\’);
end;
drop crs1-crs20 grade1-grade20;

Second example produces the retention results for each student based on whether students come back next year. cumm_crYr variables the accumulative credits at the end of year X of the program; ProgramYr variables are the name of the program the students registed at the end of year X.  If the student didn’t register for the year, there will be no value in cummcrYr or programYr. Therefore, if the student has accumulative credits calculated for the next year then the student is retained one year later and the isRetainYr1 will equal to 1.  The array evaluates whether the value for cumm_Yr2/programYr2 is null, if not, then is RetainYr2 is 1, etc.

data two;
set one;
length isRetainYr1-isRetainYr4 8;
array cumm_crYr{5} cumm_crYr1-cumm_crYr5;
array isRetainYr{4} isRetainYr1-isRetainYr4;
array programYr{5} programYr1-programYr5;
do i = 1 to 4;
j=i+1;
if pgmentyr*1 +i -1 <= 2016 then do;
if cumm_crYr{j} ne . or programYr{j} ne ” then isRetainYr{i} = 1;
else isRetainYr{i} = 0; end;
end;
run;

SAS Regression Basic 1

Step 1: Organize dataset – from wide to long. Categorical variables, possible predictor/explanatory/independent variables and the outcome/response/dependent variable all in columns.

Step 2: Proc Means – Find descriptive statistics for all the numeric variables by categorical variables.

%macro mean (data=, byvar=, var=);
proc sort data = &data.;
by &byvar.;
run;
proc means data = &data.;
by &byvar.;
var &var.;
run;
%mend mean;

Step 3: Proc Sgpanel or Proc sgplot – Check the scatter plot of outcome variable and predictor variable by categorical variables.

%macro plotby (data=, title=, by= , x=, y= , lbl= );
proc sgpanel data =&data.;
title &title.;
panelby &by. / columns =2 rows =4;
scatter x = &x. y=&y. /datalabel=&lbl.;
run;
%mend plotby;
  • title macro variable needs to be in “”;
  • by mcro variable is categorical;
  • Use COLUMNS and ROWS options in the PANELBY statement to define the grid layout of the plot.  It can be determined by the number of category and the preferred size of the plot;
  • Use uniscale in the PANELBY statement if the shared column or row axis needs to be identical. The default is ALL. UNISCALE= COLUMN | ROW | ALL
  • To give label for each datapoint in the plot, use DATALABEL option in the SCATTER statement;
  • Use ROWAXIS and COLAXIS to change the default axis value;

 

SAS Error: Java proxy is not responding

reference: http://support.sas.com/kb/44/853.html

Two check-points for Java environment set up for SAS 9.3.

  • C:\Program Files\SASHome\sassw.config
    • REHOME=C:\Program Files (x86)\Java\jre1.6.0_24\bin\java.exe
  • C:\Program Files\SASHome\x86\SASFoundation\9.3\nls\en\sasv9.cfg
    • Dsas.jre.libjvm=C:\Program Files (x86)\Java\jre7\bin\client\jvm.dll

In the second check point, the Dsas.jre setup is crucial because when updating java to a higher version, the jvm.dll will be deleted and the newer version of java doesn’t have the jvm.dll.  In the sasv9.cfg, the directory for Dsas.jre.libjvm should be point to the older version where the jvm.dll file still exists.

Download previous version from a third party link here:

https://support.sas.com/en/documentation/third-party-software-reference/9-3/support-for-jre.html

Diagnostics:

  1. run notepad as administrator
  2. open C:\Program Files\SASHome\x86\sassw.config
  3. verify the JRE that was used during installation process.
  4. current JRE configuration is JREHOME=C:\Program Files (x86)\Java\jre1.6.0_24\bin\java.exe
  5. Go to windows explorer and find java.exe doesn’t exist in the folder.
  6. There is another version of java under the jdk1.7.0_55 folder. This is a development package.  It doesn’t contain the jvm.dll file for SAS to access a JVM for JNI processing
  7. Download jre6 for windows at the third party link above. Pick the version that the filename contains “jre” and “i586”. I download the “Windows Offlline Installation” version under “Java SE Runtime Environment 6u22” which works.  The filename of the installation file is jre-6u22-windows-i586.exe.
  8. Install java run envirnoment.
  9. Use notepad to exit sasv9.cfg and update the directory for dsas.jre.libjvm to Dsas.jre.libjvm=C:\Program Files (x86)\Java\jre6\bin\client\jvm.dll
  10. The Java error message in sas disappeared.

SAS: Dictionary Columns

Get the variable names from the table and assign to macro variable var1- varn. Need to get the number of variables first to a macro varialbe &num_vars.

proc sql;
select distinct(name) into :var1-:var%trim(%left(&num_vars))
from dictionary.columns
where libname="WORK" and memname= "TABLE1" and type= "num";
quit;
  • The result is &var1 is varname1, &var2 is varname2 etc.
  • use type = “num” to get the varnames only for numeric variables;
  • use type = “char” to get the varnames only for character variables.

Layout of dictionary.columns.

Alphabetic List of Variables and Attributes
# Variable Type Len Label
10 format Char 49 Column Format
12 idxusage Char 9 Column Index Type
11 informat Char 49 Column Informat
9 label Char 256 Column Label
6 length Num 8 Column Length
1 libname Char 8 Library Name
2 memname Char 32 Member Name
3 memtype Char 8 Member Type
4 name Char 32 Column Name
15 notnull Char 3 Not NULL?
7 npos Num 8 Column Position
16 precision Num 8 Precision
17 scale Num 8 Scale
13 sortedby Num 8 Order in Key Sequence
18 transcode Char 3 Transcoded?
5 type Char 4 Column Type
8 varnum Num 8 Column Number in Table
14 xtype Char 12 Extended Type

SAS Dictionary Members and Tables

Get a full list of the dictionary tables in the working session.

PROC SQL;
SELECT *
FROM dictionary.members;
QUIT;

There are following fields in the dictionary.members.

  • libname
  • memname
  • memtype
  • dbms_memtype
  • engine
  • index
  • path

Get number of numeric variables in a table.

Use nvar to get number of variables; use num_numeric to get numeric variables; use num_character to get character variables.

proc sql ;
select num_numeric into :num_vars
from dictionary.tables
where libname=”WORK” and memname=”TABLE1″;
quit;

Note:

  • Capitalize the input for libname and memname
  • Put the input for libname and memname in “”

Layout of dictionary.tables

Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat Label
26 attr Char 3 Data Set Attributes
35 audit Char 3 Audit Trail Active?
37 audit_admin Char 3 Audit Admin Image?
36 audit_before Char 3 Audit Before Image?
39 audit_data Char 3 Audit Data Image?
38 audit_error Char 3 Audit Error Image?
19 bufsize Num 8 Bufsize
13 compress Char 8 Compression Routine
7 crdate Num 8 DATETIME. DATETIME. Date Created
28 datarep Char 32 Data Representation
33 datarepname Char 170 Data Representation Name
4 dbms_memtype Char 32 DBMS Member Type
20 delobs Num 8 Number of Deleted Observations
34 encoding Char 256 Data Encoding
14 encrypt Char 8 Encryption
16 filesize Num 8 Size of File
25 gen Num 8 Generation number
27 indxtype Char 9 Type of Indexes
1 libname Char 8 Library Name
24 maxgen Num 8 Maximum number of generations
23 maxlabel Num 8 Longest label
22 maxvar Num 8 Longest variable name
5 memlabel Char 256 Data Set Label
2 memname Char 32 Member Name
3 memtype Char 8 Member Type
8 modate Num 8 DATETIME. DATETIME. Date Modified
21 nlobs Num 8 Number of Logical Observations
9 nobs Num 8 Number of Physical Observations
15 npage Num 8 Number of Pages
40 num_character Num 8 Number of Character Variables
41 num_numeric Num 8 Number of Numeric Variables
11 nvar Num 8 Number of Variables
10 obslen Num 8 Observation Length
17 pcompress Num 8 Percent Compression
12 protect Char 3 Type of Password Protection
32 reqvector Char 24 $HEX48. $HEX48. Requirements Vector
18 reuse Char 3 Reuse Space
31 sortchar Char 8 Charset Sorted By
29 sortname Char 8 Name of Collating Sequence
30 sorttype Char 4 Sorting Type
6 typemem Char 8 Data Set Type

ODS Style Gallery

SAS results can be output to various forms of outputs, like Excel, Word, PDF, PowerPoint etc. Depending on the type of ods used, different style option can be applied.  The following link is the ods style gallery for reference.

http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p14qidvs5xf7omn14ommvsuhvmzn.htm

SAS Formats: save and apply

Reference: http://support.sas.com/resources/papers/proceedings12/048-2012.pdf

Save a Format:

Libname sasformat 'c:\sas\format';

Define Catalog:

Proc Format Library = sasformat.format1;

Copy formats in work library catalog to local permanent drive.

Proc catalog catalog=work.formats;
copy out=sasformat.formats;
run;

Check All the Formats in the Catalog:

Proc Catalog Catalog = sasformat.format1;
contents;
quit;

Check details of specific format (in the output window) in the Catalog:

Proc format fmtlib lib=sasformat.format1;
select $gender; run;

Output details of specific format in the Catalog to a dataset:

Proc format cntlout=gender lib=sasformat.format1;
select $gender; run;

Output multiple formats in the Catalog to a dataset:

Proc format cntlout=checkfmt lib=sasfmt.fmt;
select $sexfmt  $country $prorfmt $cuncfmt; 
run;

Setup Format Search Directory:

Options Fmtsearch = (sasformat.format1 work);

Turn Format Search Off:

Options Fmtsearch = ();

Hide Format Error Message and Format Error Note:

Options Fmtsearch = (sasformat.format1 work) NOFMTERR NONOTES;

Determine What Format Library is used for Format Search:

Proc Options option = FMTSEARCH;

Multiple Formats for Same Variable:

  • format name in the same library should unique;
  • format name is part of the definition of the varialbe that use the format.
  • format name is the only connection of the variable to the catalog.
  • by applying the same format name from different format libraries and catalogs in the fmtsearch option, you can have more than one format for each variable without changing the format name in the format statement in the data step. eg. english and french format for the same dataset.
Options Fmtsearch = (sasformat.englishfmt);
Options Fmtsearch = (sasformat.frenchfmt);

Delete Format Catalog:

Proc Dataasets Library=sasformat;
Delete englishfmt (memtype=catalog);
Delete frenchfmt (memtype = catalog);
Quit;

Apply Formats of Variables in a Dataset:

In a data step:

Format Var1  Formatname1 Var2 Formatname2;

Delete Formats of Variables in a Dataset:

In a data step:

Format Var1 Var2;
Format _all_;

Select and Exclude with Proc Format:

/* Select specific format, character format need to have $ prefix*/
/* adm is character format and ref is numeric format */
proc format library=formatlib cntlout=fmtname;
select $adm ref;
run;
/* Exclude specific format */
proc format library=formatlib cntlout=fmtname;
exclude $adm lastref;
run;

Note that Select and Exclude can not be used together in one Proc Format procedure.

SAS: PROC Format using dataset to create multiple formats

The following codes create two department formats: one formats ‘key’ variable to ‘deptid’ variable; the other formats ‘deptid’ variable to ‘deptname’ variable. Output the format in the work library. The convenience of the data step approach is to include all the related formats in one data set, eg. fm_dept. When the formats are required, simply using proc format and cntlin=dataset option to activate the formats.

data fm_depts;
set map_to_depts (keep = key deptid deptname);
length fmtname $ 8 type $ 1 label $70 start $ 10;
fmtname='$mapdepc';
start=key;
label=deptid;
type='c';
output;
fmtname='$mapdepn';
start=deptid;
label=deptname;
type='c';
output;
keep start fmtname label type;
run;
proc sort nodupkey; by fmtname start; run;
proc format library = work cntlin= fm_depts; run;

Format can also be created manually in the catalog as follows. A format catalog can contain many formats.

libname sasfmt  'X:\Work\SAS\data source\format';
proc format library =sasfmt.commonfmt;
run;
options fmtsearch = (sasfmt.commonfmt);
proc format library=sasfmt.commonfmt ;
value
$year
'2015' = '15'
'2016' = '16'
'2017' = '17'
'2018' = '18;
value
$ftpt
'FT' = '1'
'PT' = '2'
;
run;

SAS: Compare two datasets

Use PROC COMPARE  to compare attributes and variable existence of two dataset at multiple levels without using Data Step.

Results

Comparison of dataset1 with dataset2

(Method=EXACT)
Data Set Summary: Compare the number of variables, varibles with different labes, and number of observations
Variables Summary: number of same variables on both datasets; number of variables in one dateset but not the other.
Listing of Common Variables with Conflicting Types:
listing of Common Variables with Different Attributes: list variables with different length, formats, informats, and labels
Observation Summary
Values Comparison summary
Variables with Unequal Values
Value Comparison Results for Variables

options
NOVALUES to compare just the meta data
BRIEF to get a concise output to omit the variables with unequal values from the output.
LISTVAR to get a listing of the variables that are unique to one dataset or the other

meta data comparison:
proc compare base = dataset1 compare = dataset2 NOVALUES LISTVAR;
run;

record level comparison:

proc sort data= dataset1;
by studentid course;
proc sort data= dataset1;
by studentid course;
proc compare base = dataset1 compare = dataset2 out=check outnoequal;
by studentid courseid;
run;

OUTNOEQUAL: suppresses the writing of observations when all values are equal.  Only show the differences.