Harizontal High-Low Plot

/* Set graphics options */
goptions reset=all cback=white border;

/* Set system options */
data a;
input TEST $8. BREAKS;
cards;
Cold 5
Cold 12
Cold 14
Cold 22
Cold 52
Heat 20
Heat 25
Heat 10
Heat 22
Heat 47
Gases 12
Gases 25
Gases 33
Gases 48
Gases 24
Pressure 10
Pressure 12
Pressure 14
Pressure 22
Pressure 60
Xrays 20
Xrays 25
Xrays 14
Xrays 22
Xrays 29
Humidity 20
Humidity 25
Humidity 33
Humidity 40
Humidity 24
;
run;

/* Sort data by variable TEST */
proc sort; by TEST; run;

/**************************************************/
/* Create an output data set, B using PROC MEANS */
/* that contain new variables, MEAN, STD, STDERR, */
/* MIN, and MAX. */
/**************************************************/

proc means mean std stderr min max data=a;
by TEST;
output out=b mean=mean min=min max=max;
run;

/****************************************************************/
/* Create an annotate data set, ANNO to draw the bars at +/- 1, */
/* 2, or 3 Standard Deviation or Standard Error of the mean. */
/****************************************************************/

data anno;
retain xsys ysys '2' when 'a';
length color function $8 ;
set b;

/* Draw the horizontal line from min to max */
function='move'; xsys='2'; ysys='2'; yc=TEST; x=min; color='blue'; output; function='draw'; x=max; color='blue'; size=2; output;

/* Draw the MEAN horizontal line making the SIZE bigger */
function='move'; xsys='2';ysys='2';yc=TEST;x=mean; color='red'; output; function='draw'; x=mean; ysys='9'; y=+2; size=4; output;
function='draw'; x=mean; y=-4; size=4; output;

/* Draw the line for the MIN value */
function='move';xsys='2';ysys='2';yc=TEST;x=min;color='red';output;
function='draw';x=min;ysys='9';y=+2;size=2;output;
function='draw';x=min;y=-4;size=2;output;

/* Draw the line for the MAX value */
function='move';xsys='2';ysys='2';yc=TEST;x=max;color='red';output;
function='draw';x=max;ysys='9';y=+2;size=2;output;
function='draw';x=max;y=-4;size=2;output;

axis1 order=(0 to 100 by 10);
symbol1 i=none v=none c=black;

proc gplot data=b ;
plot test*mean / anno=anno haxis=axis1 href=30 60 90; /* The HREF= option draws reference lines */
run;
quit;

Use DO WHILE to read in every word in a string

%local cnt tword ntot1;

%let cnt=1;
%let tword=%qscan(&tstring,&cnt,%str( ));
%* let ntot1=%qscan(&tcount,&cnt,%str( ));

%do %while(&tword ne);

      data _null_;
            set &dir..&dsn;
            if &tname="&tword";
            call symput('twlbl',trim(left(&tlname)));
      run;

      %concat; *** Macro program is appended after Macro descrip;

      %let cnt=%eval(&cnt+1);
      %let tword=%qscan(&tstring,&cnt,%str( ));
      %* let ntot1=%qscan(&tcount,&cnt,%str( ));

%end; *** End of While(tword ne);

%let cnt=%eval(&cnt-1);

Define delimiter in PROC IMPORT

DSD DLM="|" TRUNCOVER LRECL=4096

proc import datafile="..." dbms=dlm out=... replace;
      delimiter='|';
      getnames=yes;
run;

Variables names to avoid when create Oracle tables from SAS

Oracle Reserved Words

Generate RTF, XML, PDF, and Excel files with ODS and SAS/IntrNet

SAS IntrNet

Label variable with special character

Create delta in the report and label:

ods escapechar='^';
ods rtf file='temp.rtf'; *** Can be HTML or PDF too;

proc report data=sashelp.class nowd ps=40;
      col age new;
      define new / format=$30. ;
/* for the RTF */
      compute new/char ;
            new='^S={font_face=symbol} D';
      endcomp;
/* for the listing */
      label age="my label" 'F0'X ;
run;

ods rtf close;


Create superscript:

Get superscripts for 0, 1, 2, and 3 in printed SAS output by using the extended characters specified in hexadecimal. The following example shows the superscripts for 0, 1, 2, and 3 using 'b0'x, 'b9'x, 'b2'x 'b3'x respectively:

data _null_;
      file print;
      put 'This is a superscript zero' 'b0'x;
      put 'This is a superscript one' 'b9'x;
      put 'This is a superscript two' 'b2'x;
      put 'This is a superscript three' 'b3'x;
run;

proc print label data=sashelp.class;
      label age=age'b2'x;
run;

Change background color of Xterm window

xterm -sb -fn fixed -ls -display $DISPLAY -bg honeydew3 &

Rename all vars in a dataset using SASHELP

*Create a temporary dataset... DSN;
data dsn;
a=1;
b=2;
c=3;
d=4;
e=5;
f=6;
run;


%macro test(lib,dsn);

*/1)*/ data _null_;
      set sashelp.vtable(where=(libname="&LIB" and memname="&DSN"));
      call symput('nvars',nvar);
run;

*/2)*/ data dsn;
      set sashelp.vcolumn(where=(libname="&LIB" and memname="&DSN"));
      call symput(cats("var",_n_),name);
run;

*/3)*/ proc datasets library=&LIB;
      modify &DSN;
      rename
      %do i = 1 %to &nvars;
            &&var&i=Rename_&&var&i.
      %end;
      ;
quit;
run;
%mend;

%test(WORK,DSN);

Generate random numbers

options pageno=1 nodate ls=80 ps=64;

data u1(keep=x);
      seed = 104;
      do i = 1 to 5;
            call ranuni(seed, x);
            output;
      end;
      call symputx('seed', seed);
run;

data u2(keep=x);
      seed = &seed;
      do i = 1 to 5;
            call ranuni(seed, x);
            output;
      end;
run;

data all;
      set u1 u2;
      z = ranuni(104);
run;

proc print label;
      label x = 'Separate Streams' z = 'Single Stream';
run;

Output from the CALL RANUNI Routine
              Separate      Single
Obs      Streams      Stream

1      0.23611      0.23611
2      0.88923      0.88923
3      0.58173      0.58173
4      0.97746      0.97746
5      0.84667      0.84667
6      0.80484      0.80484
7      0.46983      0.46983
8      0.29594      0.29594
9      0.17858      0.17858
10      0.92292      0.92292

Create FORMAT library from a SAS dataset

data fmt(keep=fmtname start label);
      set temp;
      fmtname="TRTFMT";
      start=trtgrp;
      label=trtlbl;
run;

proc format library=work cntlin=fmt;
run;

=================
TEMP:
trtgrp      trtlbl
1            Treatment 1
2            Treatment 2
3            Placebo

PROC FCMP

PROC FCMP

Boostraps Using SAS

Much Faster Boostraps

Delete single format from library

proc catalog catalog=library.formats;
        delete yn.format dnk.formatc;
quit;

*** yn is numeric format, dnk is character format;

Macros for Standard Date/Time Processing

SAS Macro to process date/time

ISO 8601 Date Time Format

ISO 8601

What's new is BASE SAS 9.2

Bayesian Modeling in SAS

Bayesian Modeling

proc bgenmod

Handling leading, trailing blanks in macro variable

If a macro variable contains leading or trailing blanks, they can be easily removed without the use of %LEFT or %TRIM. Simply:

%let macvar = &macvar;

KUPDATE - replace nth position

KUPDATE(argument,position,n <, characters-to-replace > )
KUPDATE(argument,position <,n > , characters-to-replace)
Inserts, deletes, and replaces character value contents.
Product: Base SAS
Document: SAS National Language Support: Reference Guide

Link: kupdate

Function to get file path & name

%sysfunc(getoption(sysin));

Used in title of footnote or PUT statement to display the location of SAS source code.

SYSIN a system option, can be look up in table sashelp.voption under UNIX.

Link: getoption
Link: sysin

Ways to find duplicate

1. proc sort

2. group by, class, (not sorted)

3. sql distinct

4. unique index

Read CSV file with carriage return characters

/* Replace carriage return and linefeed characters inside */
/* double quotes with a specified character. This sample */
/* uses '@' and '$', but any character can be used, including */
/* spaces. CR/LFs not in double quotes will not be replaced. */

%let repA=' '; /* replacement character LF */
%let repD=' '; /* replacement character CR */

%let dsnnme="/home/help/zzz.csv"; /* use full path of CSV file */

data _null_;
/* RECFM=N reads the file in binary format. The file consists */
/* of a stream of bytes with no record boundaries. SHAREBUFFERS */
/* specifies that the FILE statement and the INFILE statement */
/* share the same buffer. */

infile &dsnnme recfm=n sharebuffers;
file &dsnnme recfm=n;

/* OPEN is a flag variable used to determine if the CR/LF is within */
/* double quotes or not. Retain this value. */

retain open 0;

input a $char1.;
/* If the character is a double quote, set OPEN to its opposite value. */
if a = '"' then open = ^(open);

/* If the CR or LF is after an open double quote, replace the byte with */
/* the appropriate value. */

if open then do;
if a = '0D'x then put &repD;
else if a = '0A'x then put &repA;
end;
run;

filename infle '/home/help/zzz.csv' termstr=CRLF;

PROC IMPORT OUT= zzz DATAFILE= infle
DBMS=CSV REPLACE;
GUESSINGROWS=1000;
GETNAMES=YES;
DATAROW=2;
RUN;

MSGLEVEL option

MSGLEVEL=I option prints additional SAS INFO messages related to index usage, merge processing, sort utilities, and CEDA usage, into your SAS log; as well as the regular SAS NOTE, WARNING, and ERROR messages.

The default is MSGLEVEL=N.

OPTIONS MSGLEVEL=I;

See also

Read all files in one directory

%macro readraw(dir=.);

%local fileref rc did dnum dmem memname;
%let fileref=thisdir;
%let rc=%sysfunc(filename(fileref,&dir));
%let did=%sysfunc(dopen(&fileref));
%let dnum=%sysfunc(dnum(&did));

%do dmem=1 %to &dnum;
%let memname=%sysfunc(dread(&did, &dmem));
%if %upcase(%scan(&memname,-1,.)) = DAT %then %do;
%let dataset=%scan(&memname,1,.);
data &dataset;
infile "&dir\&memname";
input Course_code $4. Location $15. Begin_date date9.;
format Begin_date date9.;
run;

proc print data=&dataset;
title "%trim(&syslast)";
run;
%end;
%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(fileref));

%mend readraw;

options mprint;
%readraw(dir=c:\workshop\winsas\amacr);

SAS training videos

Videos

Efficient programming

Link: http://support.sas.com/resources/papers/proceedings09/041-2009.pdf

Cancel titles

A statement to cancel the titles:

title;

RSASUSER System Option: OpenVMS

To delete the following message in the log, try the command line option -RSASUSER, indicate SASUSER is for READ-only.

“WARNING: Unable to copy SASUSER registry to WORK registry. Because of this, you will not see registry customizations during this session.”

Link: RSASUSER

Proc Import trick

PROC IMPORT DATAFILE="c:\sas\ego.csv" OUT=jeeshim.egov  

             DBMS=CSV REPLACE; 
             guessingrows = 1000;

run;

GUESSINGROWS=1 to 32767;
specifies the number of rows of the file to scan in order to determine the appropriate data type and length for the columns. The scan data process scans from row 1 to the number that is specified by GUESSINGROWS=. The default value is 20. However, you can change the default value in the SAS Registry under SAS REGISTRY -> PRODUCTS -> BASE -> EFI -> GuessingRows.

Default:	20
Requirement:	This number should be greater than the value that is specified for DATAROW=.

Exporting data using ODS tagsets

Link: http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html#export

SAS Papers Collection

Here

Base SAS Tip Sheets

Here

Use of colon

Link: www2.sas.com/proceedings/sugi29/054-29.pdf

Combining Queries with Set Operators

Link: UNION, EXCEPT, INTERSECT, OUTER UNION, etc.

ANY* and NOT* Function

The ANY* and NOT* functions find specific characters within a character string. The set of characters that the functions find is based on the translation table used by SAS, and whether the session is run in an ASCII or EBCDIC environment. To determine which characters are found in your session of SAS, use the following program. Values of 1 mean that the character is found by the function, values of 0 mean the character is not found.

data test;
      do dec=0 to 255;
           byte=byte(dec);
           hex=put(dec,hex2.);
           anycntrl=anycntrl(byte);
           anygraph=anygraph(byte);
           anypunct=anypunct(byte);
           notalpha=notalpha(byte);
           notprint=notprint(byte);
         output;
      end;
run;

proc print data=test;
run;

PROC SURVEYSELECT

Simple random sample without replacement
Select a random sample where no observation can be chosen more than once.

/* WORK.EASTHIGH is a data base of student grade point averages */
/* from East High School, Grades 9 through 12, 100 or more students per grade. */

data EastHigh;
      format GPA 3.1;
      do Grade=9 to 12;
            do StudentID=1 to 100+int(201*ranuni(432098));
                  GPA=2.0 + (2.1*ranuni(34280));
                  output;
            end;
      end;
run;

/* Using PROC SURVEYSELECT */
/* Use METHOD=SRS. N= is the number of observations to select. */
/* The sample is stored in the OUT= data set, SAMPLE1. */

proc surveyselect data=EastHigh method=srs n=15 out=sample1;
run;

title "PROC SURVEYSELECT";
proc print data=sample1;
run;


Simple random sample with replacement
Select a random sample where an observation can be chosen more than once.

/* Using PROC SURVEYSELECT */
/* Use METHOD=URS. N= is the number of observations to select. */
/* The sample is stored in the OUT= data set, SAMPLE. */
/* The OUTHITS option includes an observation in the OUT= data set */
/* for each selected unit. By default, the OUT= will contain one */
/* observation for each unique selected unit and the NumberHits */
/* variable identifies the number of times each unit is selected. */

proc surveyselect data=EastHigh method=urs n=15 out=sample outhits;
run;

title "PROC SURVEYSELECT ";
proc print;
run;

Jackknife and Bootstrap Analyses

Link: http://support.sas.com/kb/24/982.html

Look up system options and display setting

proc print data=sashelp.voption;

run;

proc options group=memory;

run;

proc options option=memblksz define value lognumberformat;

run;

Link: http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000083867.htm

Superscript, Subscript in RFT

data test;
length secnam $15;input sortord secnam $ pvalue; 
cards;
   1 demog 0.8812
   2 ae 0.7112
   3 disposition 0.8112
   4 medicalhistory 0.9112
; 
run;
 
ods listing close;
ods rtf file="Test.rtf" style=rtfout;
ods escapechar='\'; *** value not limited to \;

proc report data = test missing split="$" spacing=0 headline nowd;
     column sortord secnam pvalue;
     define sortord / order noprint;
     define secnam / order flow "Demographics$Variable\{super a}";
     define pvalue / display flow "ANOVA$P-Value\{sub p}";
footnote1 "testing\{super 1}";
footnote2 "new test\{sub 2}";
run;

ods rtf close;
ods trace off;
ods listing;

Link: http://support.sas.com/rnd/base/ods/templateFAQ/Template_rtf.html#escapechar

Changing titles in ODS Content Page

For those who use the HTML content frame, it can be quite annoying to see a bunch of PROC PRINT or PROC GPLOT in the content page. To change the titles, you can use the following statement.

ODS PROCLABEL ""; 

The PROCLABEL statement also has utility in the PDF destination as it can be used to alter the text in the contents panel.

SAS Qualification Tools

Using two tools to verify SAS installed correctly and operational. The two tools are the SAS Installation Qualification Tool (SAS IQ) and the SAS Operational Qualification Tool (SAS OQ).

• The SAS IQ assists in demonstrating the SAS System has been installed and maintained to the manufacturer's specifications. SAS IQ verifies the integrity of each file in the SAS System 9 and provides the customer a set of reports detailing the results.
• The SAS OQ assists in demonstrating the SAS System is operational. SAS OQ uses SAS programs provided by the component development groups and will execute, process, and report the program results. 

SAS 9.1.3  SAS 9.2

Copy a permanent format library

proc catalog catalog=libA.formats;
        copy out=libB.formats;
run;

Creating a Stored Macro Facility

options mstored sasmstore=macro;
libname macro '/home/dltmp/maclib';

%macro test / store des='This is my program';
         %let x=1;
%mend;

%copy test/source;

proc catalog cat=macro.sasmacr;
        contents;
run;

Link: http://www2.sas.com/proceedings/forum2008/101-2008.pdf

Handle special character when import data

sasv9 -set NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252 crdata.sas


Link: http://support.sas.com/kb/18/688.html

Customize Axes in PROC GPLOT

Link: www2.sas.com/proceedings/sugi25/25/cc/25p110.pdf

Utilize SAS Audit Files to Keep Track of SAS Data Set Updates

You can direct SAS to keep an audit trail of all of the updates made to a SAS data set. This is particularly handy if you need to determine who updated a SAS data set at any given time for a security review or a data integrity audit. The audit trail file allows you to track patterns of when particular data were added, modified, or deleted. You may even be able to fashion your own update roll-back program from the audit file.

Creating an audit trail file for a SAS data set is easily done via the DATASETS Procedure.

proc datasets library=ctemp;
        audit orsales;
        initiate;
run;
quit;

Executing the code results in a SAS audit file named orsales.sas7baud being built in the CTEMP SAS data library. When updates are made to the orsales SAS data set, entries are made to the attendant orsales SAS audit file.

proc contents data=ctemp.orsales(type=audit);
run;

proc print data=ctemp.orsales(type=audit);
run;

You can determine the variables available in your audit file or print the audit file observations using PROC CONTENTS and PROC PRINT, respectively, using the TYPE=AUDIT data set option.

Note that the Member Type in the Attributes section of the CONTENTS output will state "AUDIT", and that you will get a few additional audit-related variables. You can use other SAS procedures, such as PROC REPORT, on the SAS audit file, too.

The basics of PROC TRANSPOSE

Link: support.sas.com/resources/papers/proceedings09/060-2009.pdf

Jump start in PROC REPORT

PROC REPORT coding can get verbose, especially if there are a lot of variables requiring DEFINE statements. Here is a little trick which may speed things up a bit.

First, run PROC REPORT against the data set of interest, including the LIST option to list the code in the log and the NOEXEC option to prevent SAS from actually running the code. For example:

proc report data = sashelp.class list noexec ;
run ;

This should appear in the log:

PROC REPORT DATA=SASHELP.CLASS LS=96 PS=55 SPLIT="/" CENTER ;
    COLUMN Name Sex Age Height Weight;
    DEFINE Name / DISPLAY FORMAT= $8. WIDTH=8 SPACING=2 LEFT "Name" ;
    DEFINE Sex / DISPLAY FORMAT= $1. WIDTH=1 SPACING=2 LEFT "Sex" ;
    DEFINE Age / SUM FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Age" ;
    DEFINE Height / SUM FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Height" ;
    DEFINE Weight / SUM FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Weight" ;
RUN;

Copy the code to the program editor, and make any needed changes. Suppose we want average heights and weights by sex and age. Just insert the NOWD option in the PROC statement (assuming that interactive windowing is not needed), comment out the reference to NAME in the COLUMN statement and the DEFINE statement for NAME, and change the first option in each of the remaining DEFINE statements:

PROC REPORT DATA=SASHELP.CLASS LS=96 PS=55 SPLIT="/" CENTER nowd ;
    COLUMN /* Name */ Sex Age Height Weight;
*   DEFINE Name / DISPLAY FORMAT= $8. WIDTH=8 SPACING=2 LEFT "Name" ;
    DEFINE Sex / group FORMAT= $1. WIDTH=1 SPACING=2 LEFT "Sex" ;
    DEFINE Age / group FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Age" ;
    DEFINE Height / mean FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Height" ;
    DEFINE Weight / mean FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Weight" ;
RUN;

The output:

S
e
x Age Height Weight
F 11 51.3 50.5
    12 58.05 80.75
    13 60.9 91
    14 63.55 96.25
    15 64.5 112.25
M 11 57.5 85
    12 60.366667 103.5
    13 62.5 84
    14 66.25 107.5
    15 66.75 122.5
    16 72 150

Cosmetic refinements can be made by changing the WIDTH option for SEX and the FORMAT options for HEIGHT and WEIGHT.

Create candle stick plots

Link: http://support.sas.com/kb/24/914.html

Use EXIST to check dataset

Use a simple DATA step function named 'EXIST' to check for the existence of a certain SAS data file.

%MACRO D_EXIST(INPUT);
%SYSFUNC(EXIST(&INPUT))
%MEND D_EXIST;

The macro %D_EXIST could be called from within an expression.

%IF %D_EXIST(SASHELP.CLASS) %THEN %DO;

The function EXIST can also be used to find other type of data sets in the directory by changing the types of members that it should look for.

Encrypt Dataset

DATA exmpdat (ENCRYPT = YES PW = [password]);
SET exmpdat;
RUN;

Determining the number of observations in a SAS data set efficiently

SAS data sets consist of a descriptor portion and a data portion that contains the data values.The descriptor portion of a SAS data set contains detailed information about the data set. This information includes:

• The name of the data set and its member type
• The date and time the data set was created
• The number of observations
• The number of variables
• The engine type

Using the descriptor portion is one of the most efficient and quickest ways of determining the number of observations in a SAS data set.
Here there are some examples:

/* Before SAS V9*/
data _NULL_;
if 0 then set sashelp.class nobs=n;
call symput('nrows',trim(left(put(n,8.))));
stop;
run;
%put nobs=&nrows;
 
/* SAS V9.x and higher */
data _NULL_;
if 0 then set sashelp.class nobs=n;
call symputx('nrows',n);
stop;
run;
%put nobs=&nrows;

• The "if 0" is a a conditional statement designed to never be executed at run-time and works since the "set sashelp.class" gives the header information of the data set class to the compiler which sets up the pdv accordingly, but is skipped in the execution time because the if condition is false.

• Nobs is a SAS automatic variable which contains the number of records in the dataset named in the set statement. The code, nobs = n, places the value of nobs (the number of records in the sashlep.class dataset) in the variable n. [NOBS option limitations: 1."The number of observations includes those observations that are marked for deletion but are not yet deleted."2."For certain SAS views, SAS cannot determine the number of observations. In these cases, SAS sets the value of the NOBS= variable to the largest positive integer value that is available in your operating environment.]

• The STOP statement is used to prevent an endless loop because EOF is never reached.
  

Many times users don't need to know how many obs there are, just whether there are any. In such cases, a data step like the following might be just what is needed. This technique also has the advantage that it works for views and other data sets where the number of observations is not available in the data set header.

data _null_;
call symput('AnyObs','0'); /* set to 0 for No */
set &data(obs=1);
call symput('AnyObs','1'); /* if got here there are obs so set to 1 for Yes */
run;

The meta data can also be accessed by the macro language using DATA step functions. The ATTRN function is used with the NLOBS argument in the %OBSCNT macro shown below to retrieve the number of non-deleted observations from the meta data.

%macro obscnt(dsn);
%local nobs dsnid;
%let nobs=.;
 
%* Open the data set of interest;
%let dsnid = %sysfunc(open(&dsn));
 
%* If the open was successful get the;
%* number of observations and CLOSE &dsn;
%if &dsnid %then %do;
%let nobs=%sysfunc(attrn(&dsnid,nlobs));
%let rc  =%sysfunc(close(&dsnid));
%end;
%else %do;
%put Unable to open &dsn - %sysfunc(sysmsg());
%end;
 
%* Return the number of observations;
&nobs
%mend obscnt;

Forcing the Concatenation of Data Sets

When one or more variables in the input data set, represented by the DATA= option, are not present in the BASE= data set, or the variables in the input data set are not of the same type as the variables in the BASE= data set, an optional FORCE option can be specified on the PROC APPEND statement to force the concatenation of the data sets. Using the FORCE option also prevents an error from being generated in these situations. The next example illustrates the FORCE option being specified in the concatenation of the TXN data set to the MASTER data set.

PROC APPEND
BASE=master
DATA=txn
FORCE;
RUN;

Systask Statement

You can launch SAS programs from within a SAS program and suspend execution of the launching SAS program until the launched SAS programs complete their execution. Doing this, you can control the execution sequence of your SAS programs if/when you do not want to depend entirely upon an OS's scheduling software (e.g. CRON on UNIX and Linux, Windows Scheduler on Windows, etc.).


For example, in your BIGPROGRAM.SAS, you could have the following code:

systask command "sas your_clever_program1.sas" taskname=program1;
systask command "sas your_clever_program2.sas" taskname=program2;

waitfor _all_ program1 program2;

/* Other SAS code that processes the data sets created by the two afore-mentioned programs */

Note that the _all_ option states that your program is to wait for both program1 _AND_ program2 to complete before resuming execution. As an alternative, you could have coded _any_, which would have your program resume the moment either program1 _OR_ program2 completed execution.

Link: http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/win-stmt-systask.htm
Link: http://www2.sas.com/proceedings/sugi30/021-30.pdf

Call missing

The CALL MISSING routine assigns an ordinary numeric missing value (.) to each numeric variable in the argument list.

The CALL MISSING routine assigns a character missing value (a blank) to each character variable in the argument list. If the current length of the character variable equals the maximum length, the current length is not changed. Otherwise, the current length is set to 1.

You can mix character and numeric variables in the argument list.

prod='shoes';
invty=7498;
sales=23759;
call missing(prod,invty);
put prod= invty= sales=;

prod= invty=. sales=23759

XMRLMEM option

Obtain the amount of computer memory available to SAS with the undocumented XMRLMEM option.

data _null_;
      format amt comma20.;
      amt = input(getoption('xmrlmem'),20.);
      put amt=;
run;

The code shown above gives the total number of bytes of real memory available. (It does not count the operating system's virtual memory; only real memory). Divide this number by 1024 to get it into K's... or whatever to get it into Megs or Gigs.

XMRLMEM is an undocumented diagnostic option that can come in handy when considering allocating a bushel-full of buffers or considering doing some big-time hashing.

Link: http://www2.sas.com/proceedings/forum2007/271-2007.pdf

Seperate data into EXCEL sheets

%macro multisheet;

proc sql noprint;
      select distinct age
      into :idage1 - :idage99
      from sashelp.class;
%let agecnt = &sqlobs;
quit;

%do i = 1 %to &agecnt;
PROC EXPORT DATA = sashelp.class(where=(age=&&idage&i))
            OUTFILE="C:\temp\class_ages.xls"
            DBMS= excel
            REPLACE;
      sheet = "Age_&&idage&i";
RUN;
%END;

%mend multisheet;
%multisheet

List Oracle tables in SAS

C-TASC specific

libname oralib ORACLE user=ctpmstat path="@xxxx" password = xxxxxxx;

proc freq data = oralib.all_tables;
      tables owner * table_name/list;
run;

X Command in SAS

*** Use sas to perform Unix command: find all xpt files under the known directory, sort by the directries, output in a txt file ***;

%macro find ;

X 'find /home/xxx -name "*.xpt" -exec ls -i1 {} \; | sort > /home/xxx/zzz.txt';

%if &sysrc ne 0 %then %do ;
endsas;
%end ;

%mend find ;

%find ;

Link: http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/exittemp.htm
Link:  www.lexjansen.com/pharmasug/2005/posters/po13.pdf

SAS efficiency

This is a great tutorial on SAS efficiency.

Proc CDISC to validate SDTM

** ‘Special’ class domain: DM **;
PROC CDISC MODEL=SDTM;
SDTM       SDTMVERSION= "3.1";
DOMAINDATA DATA=WORK.DM
           DOMAIN=DM
           CATEGORY=SPECIAL;
RUN;


** ‘Events’ class domain: AE **;
PROC CDISC MODEL=SDTM;
SDTM       SDTMVERSION= "3.1";
DOMAINDATA DATA=WORK.AE
           DOMAIN=AE
           CATEGORY=EVENTS;
RUN;


** ‘Interventions’ class domain: CM **;
PROC CDISC MODEL=SDTM;
SDTM       SDTMVERSION= "3.1";
DOMAINDATA DATA=WORK.CM
           DOMAIN=CM
           CATEGORY=INTERVENTIONS;
RUN;


** ‘Findings’ class domain: IE **;
PROC CDISC MODEL=SDTM;
SDTM       SDTMVERSION= "3.1";
DOMAINDATA DATA=WORK.IE
           DOMAIN=IE
           CATEGORY=FINDINGS;
RUN;

Link: http://www.lexjansen.com/pharmasug/2008/rs/rs07.pdf
Link: http://support.sas.com/rnd/base/xmlengine/proccdisc/index.html

Startpage in ODS

STARTPAGE=NEVER | NO | NOW | YES
controls page breaks.

NEVER      specifies not to insert page breaks, even before graphics procedures.

CAUTION:
Each graph normally requires an entire page. The default behavior forces a new page after a graphics procedure. STARTPAGE=NEVER turns off that behavior, so specifying STARTPAGE= NEVER might cause graphics to overprint.

NO      specifies that no new pages be inserted at the beginning of each procedure, or within certain procedures, even if new pages are requested by the procedure code. A new page will begin only when a page is filled or when you specify STARTPAGE=NOW.

CAUTION:
Each graph normally requires an entire page. The default behavior forces a new page after a graphics procedure, even if you use STARTPAGE=NO. STARTPAGE=NEVER turns off that behavior, so specifying STARTPAGE= NEVER might cause graphics to overprint.
Alias: OFF
Tip: When you specify STARTPAGE=NO, system titles and footnotes are still produced only at the top and bottom of each physical page, regardless of the setting of this option. Thus, some system titles and footnotes that you specify might not appear when this option is specified.

NOW      forces the immediate insertion of a new page.
Tip: This option is useful primarily when the current value of the STARTPAGE= option is NO. Otherwise, each new procedure forces a new page automatically.

YES      inserts a new page at the beginning of each procedure, and within certain procedures, as requested by the procedure code.
Alias: ON

Default: YES

Link: http://analytics.ncsu.edu/sesug/2007/DP05.pdf
Link: http://www.nesug.org/Proceedings/nesug06/io/io13.pdf

Formdlim option

FORMDLIM='delimiting-character'

'delimiting-character'
specifies in quotation marks a character written to delimit pages. Normally, the delimit character is null, as in this statement:

options formdlim='';

use space will eliminate the delimitor between pages in the listing:

options formdlim=' ';

Data Step Views

Definition of a DATA Step View

A DATA step view is a native view that has the broadest scope of any SAS view. It contains a stored DATA step program that can read data from a variety of sources, including:

• raw data files
  
• SAS data files
  
• PROC SQL views
  
• SAS/ACCESS views
  
• DB2, ORACLE, or other DBMS data
  

Creating DATA Step Views

In order to create a DATA step view, specify the VIEW= option after the final data set name in the DATA statement. The VIEW= option tells SAS to compile, but not to execute, the source program and to store the compiled code in the input DATA step view that is named in the option.

For example, the following statements create a DATA step view named DEPT.A:

libname dept 'SAS-library';

data dept.a / view=dept.a;
... more SAS statements ...
run;

Note that if the SAS view exists in a SAS library, and if you use the same member name to create a new view definition, then the old SAS view is overwritten.

Beginning with Version 8, DATA step views retain source statements. You can retrieve these statements using the DESCRIBE statement. The following example uses the DESCRIBE statement in a DATA step view in order to write a copy of the source code to the SAS log:

data view=inventory;
describe;
run;  

For more information on how to create SAS views and use the DESCRIBE statement, see the DATA statement in SAS Language Reference: Dictionary.

What Can You Do with a DATA Step View?

Using a DATA step view, you can do the following:

• directly process any file that can be read with an INPUT statement
  
• read other SAS data sets
  
• generate data without using any external data sources and without creating an intermediate SAS data file.
  

Because DATA step views are generated by the DATA step, they can manipulate and manage input data from a variety of sources including data from external files and data from existing SAS data sets. The scope of what you can do with a DATA step view, therefore, is much broader than that of other types of SAS views.

Differences between DATA Step Views and Stored Compiled DATA Step Programs

DATA step views and stored compiled DATA step programs differ in the following ways:

• a DATA step view is implicitly executed when it is referenced as an input data set by another DATA or PROC step. Its main purpose is to provide data, one record at a time, to the invoking procedure or DATA step.
  
• a stored compiled DATA step program is explicitly executed when it is specified by the PGM= option on a DATA statement. Its purpose is usually a more specific task, such as creating SAS data files, or originating a report.
  

For more information on stored compiled DATA step programs, see Stored Compiled DATA Step Programs.

Restrictions and Requirements

Global statements do not to apply to a DATA step view. Global statements such as the FILENAME, FOOTNOTE, LIBNAME, OPTIONS, and TITLE statements, even if included in the DATA step that created the SAS view, have no effect on the SAS view. If you do include global statements in your source program statements, SAS stores the DATA step view but not the global statements. When the view is referenced, actual execution can differ from the intended execution.

When a view is created, the labels for the variable that it returns are also created. If a DATA step view reads a data set that contains variable labels and a label is changed after the view is created, any procedure output will show the original labels. The view must be recompiled in order for the procedure output to reflect the new variable labels.

If a view uses filerefs or librefs, the fileref or libref that is used is the one that is defined at the time that the view is compiled. This means that if you change the file that is referenced in a fileref that the view uses, the new file is ignored by the view and the file that is referred to by the fileref at the time the view was compiled continues to be used.

Performance Considerations

• DATA step code executes each time that you use a DATA step view, which might add considerable system overhead. In addition, you run the risk of having your data change between steps. However, this also means that you get the most recent data available--that is, data when the view is executed as compared to data when the view was compiled.
  
• Depending on how many reads or passes on the data are required, processing overhead increases.
  
  
  
  
  
  • When one sequential pass is requested, no data set is created. Compared to traditional methods of processing, making one pass improves performance by decreasing the number of input/output operations and elapsed time.
    
  • When random access or multiple passes are requested, the SAS view must build a spill file that contains all generated observations so that subsequent passes can read the same data that was read by previous passes. In some instances, the view SPILL= data set option can reduce the size of a spill file.
    
  
  
  

Example 1: Merging Data to Produce Reports

If you want to merge data from multiple files but you do not need to create a file that contains the combined data, you can create a DATA step view of the combination for use in subsequent applications.

For example, the following statements define DATA step view MYV9LIB.QTR1, which merges the sales figures in the data file V9LR.CLOTHES with the sales figures in the data file V9LR.EQUIP. The data files are merged by date, and the value of the variable Total is computed for each date.

libname myv9lib 'SAS-library';
libname v9lr 'SAS-library';

data myv9lib.qtr1 / view=myv9lib.qtr1;
merge v9lr.clothes v9lr.equip;
by date;
total = cl_v9lr + eq_v9lr;
run;

The following PRINT procedure executes the view:

proc print data=myv9lib.qtr1;
run;

Example 2: Producing Additional Output Files

In this example, the DATA step reads an external file named STUDENT, which contains student data, and then writes observations that contain known problems to data set MYV9LIB.PROBLEMS. The DATA step also defines the DATA step view MYV9LIB.CLASS. The DATA step does not create a SAS data file named MYV9LIB.CLASS.

The FILENAME and the LIBNAME statements are both global statements and must exist outside of the code that defines the SAS view, because SAS views cannot contain global statements.

Here are the contents of the external file STUDENT:

dutterono  MAT   3
lyndenall  MAT   
frisbee    MAT  94
SCI  95
zymeco     ART  96
dimette         94
mesipho    SCI  55
merlbeest  ART  97
scafernia       91    
gilhoolie  ART 303
misqualle  ART  44
xylotone   SCI  96   

Here is the DATA step that produces the output files:

libname myv9lib 'SAS-library';
filename student 'external-file-specification'; 1 

data myv9lib.class(keep=name major credits)
myv9lib.problems(keep=code date) / view=myv9lib.class; 2 
infile student;
input name $ 1-10 major $ 12-14 credits 16-18; 3 
select;  
when (name=' ' or major=' ' or credits=.)
do code=01;
date=datetime();
output myv9lib.problems;
end; 4 
when (0<90)
do code=02;
date=datetime();
output myv9lib.problems;
end; 5 
otherwise
output myv9lib.class;
end;
run; 6 

The following example shows how to print the files created previously. The MYV9LIB.CLASS contains the observations from STUDENT that were processed without errors. The data file MYV9LIB.PROBLEMS contains the observations that contain errors.

If the data frequently changes in the source data file STUDENT, there would be different effects on the returned values in the SAS view and the SAS data file:

• New records, if error free, that are added to the source data file STUDENT between the time you run the DATA step in the previous example and the time you execute PROC PRINT in the following example, will appear in the SAS view MYV9LIB.CLASS.
  
• On the other hand, if any new records, failing the error tests, were added to STUDENT, the new records would not show up in the SAS data file MYV9LIB.PROBLEM, until you run the DATA step again.
  

A SAS view dynamically updates from its source files each time it is used. A SAS data file, each time it is used, remains the same, unless new data is written directly to the file.

filename student 'external-file-specification';
libname myv9lib 'SAS-library'; 7 

proc print data=myv9lib.class;
run; 8 

proc print data=myv9lib.problems;
format date datetime18.;
run; 9 

	Reference a library called MYV9LIB. Tell SAS where a file that associated with the fileref STUDENT is stored.
	Create a data file called PROBLEMS and a SAS view called CLASS and specify the column names for both data sets.
	Select the file that is referenced by the fileref STUDENT and select the data in character format that resides in the specified positions in the file. Assign column names.
	When data in the column NAME, MAJOR, or CREDITS is blank or missing, assign a code of 01 to the observation where the missing value occurred. Also assign a SAS datetime code to the error and place the information in a file called PROBLEMS.
	When the amount of credits is greater than zero, but less than ninety, list the observations as code 02 in the file called PROBLEMS and assign a SAS datetime code to the observation.
	Place all other observations, which have none of the specified errors, in the SAS view called MYV9LIB.CLASS.
	The FILENAME statement assigns the fileref STUDENT to an external file. The LIBNAME statement assigns the libref MYV9LIB to a SAS library.
	The first PROC PRINT calls the SAS view MYV9LIB.CLASS. The SAS view extracts data on the fly from the file referenced as STUDENT.
	This PROC PRINT prints the contents of the data file MYV9LIB.PROBLEMS. Link: http://www2.sas.com/proceedings/sugi29/067-29.pdf

Effeciency with SAS

“??” FORMAT MODIFIER

DETERMINE IF A CHARACTER STRING CONTAINS ONLY NUMBERS USING THE INPUT FUNCTION AND THE SPECIAL "??" FORMAT MODIFIER

The following excerpt is from SAS OnlineDoc documentation: ? or ??

The optional question mark (?) and double question mark (??) format modifiers suppress the printing of both the error messages and the input lines when invalid data values are read. The ? modifier suppresses the invalid data message. The ?? modifier also suppresses the invalid data message and, in addition, prevents the automatic variable _ERROR_ from being set to 1 when invalid data are read. Below is an example of using ?? to determine whether a variable contains non-numeric values or not:

data _null_;
      x = “12345678”;
      if (input(x, ?? 8.) eq .) then
      put ‘non-numeric’;
      else put ‘numeric’;
run;

Running SAS would return “Numeric” in the above example. If we used X=”123a5678”, SAS would return “Non-Numeric”. Note that the input format in the above example is “8.” So only the first 8 bytes of the character string are checked. Thus, X=123456789a would return “Numeric” as it would only be checking the first 8 bytes of the string.

Link: http://www.nesug.org/Proceedings/nesug01/at/at1013.pdf

Change Position of Variables Using RETAIN

Data a;
      Input x y z;

           Cards;
           1    2    3
           ;
     Run;

Data b;
      Retain z y x;

           Set a;
     Run;