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;

COUNTW Function (SAS 9.2)

Count Words in a String

proc sort data=sashelp.zipcode(keep=statename)
out=allnames nodupkey;
by statename;
run;
data words;
set allnames;
Words=countw(statename," ");
run;
proc freq data=words;
tables Words;
title1 "Number of Words in U.S. State and Territory Names";
run;

SAS Log

3999   proc sort data=sashelp.zipcode(keep=statename)
4000             out=allnames nodupkey;
4001     by statename;
4002   run;

NOTE: There were 41763 observations read from the data set SASHELP.ZIPCODE.
NOTE: 41707 observations with duplicate key values were deleted.
NOTE: The data set WORK.ALLNAMES has 56 observations and 1 variables.

4003   data words;
4004     set allnames;
4005     Words=countw(statename," ");
4006   run;

NOTE: There were 56 observations read from the data set WORK.ALLNAMES.
NOTE: The data set WORK.WORDS has 56 observations and 2 variables.

4007   proc freq data=words;
4008     tables Words;
4009     title1 "Number of Words in U.S. State and Territory Names";
4010   run;

NOTE: There were 56 observations read from the data set WORK.WORDS.

SAS Listing Output

Number of Words in U.S. State and Territory Names

The FREQ Procedure

Cumulative    Cumulative
Words    Frequency     Percent     Frequency      Percent
----------------------------------------------------------
1          42       75.00            42        75.00
2          12       21.43            54        96.43
3           2        3.57            56       100.00

Unix Commands

1. basic:

vi temp.sas:
edit mode for temp.sas;

more temp.sas:

view temp.sas:
read only;

rm temp.sas: delete temp.sas [rm ~\$zzz.doc (delete temporary file)];

mkdir test:
create directory called test;

rmdir test:
remove directory test, test needs be emptied first;

rm -r test:
remove directory test, test doesn't need to be emptied first;

cd ..:
go to the directory one level up;

cd tmpdir:  
go to directory called tmpdir;

cp temp.sas temp1.sas:
copy temp.sas as temp.sas;

2. save+quit:

:ZZ-->at the end of edit line; or,

:wq;

:x;

3. quit w/o saving:

:e! (still in the edit windows)

:q! (quit edit windows)

4.
1).             change delete/cut copy
__________________________________________

1 letter         r      x          yl

5 letter         5s     5x         5yl

1 word           cw     dw         yw

5 words          5cw    5dw        5yw

1 line           cc     dd         yy

5 lines          5cc    5dd        5yy

to line begining c0     d0         y0

to line end      c$     d$         y$

3 words back     3cb    3db        3yb

2).

__________________________________________
move to line 6:                 :6 or 6G

change aaa to bbb on line 2:    :2s/aaa/bbb;

to the beginning:               0;

to the end:                     $;

10 letters lowcase<-->upcase:   10~;
Join 5 lines:                   5J;
insert:                         i or a;
repeat:                         .;

paste:                          p;

top line of screen:             H;

last line of screen:            L;

middle line of screen:          M;

find aaassss:                   /aaassss; n;

3). combinations:

delete     copy      from cursor to

__________________________________________

dH         yH        top of screen

dL         yL        end of screen

dG         yG        end of file

d+         y+        next line

d13G       y13G      13 line

4). recover large delete (upper to 9):

"1p: recover last deletion

"5p: recover fifth-to-last deletion

5). name own buffer (upper to 9):

"add: delete current line and save in buffer a;

"a7dd: delete 7 lines from current cursor position and save in buffer a;

"a7yy: copy 7 lines from current cursor position and save in buffer a;

"ap: paste buffer a information from current cursor position;

6). ex editor:

:3,18d    delete lines 3 through 18;
:3,18m23  move lines 3 through 18 after line 23;

:3,18co23 copy lines 3 through 18 and paste after line 23;

:=        total line number;

:.,$d     delete current line ( . ) to the end of file ( $ );
:20,.m$   move line 20 through current line ( . ) to the end ( $ );

:%d       delete all lines ( % );

:%co$     copy all lines ( % ) and paste to the end ( $ );

:-,+co0   copy three lines to the top (0);

:230,$w temp.sas   save 230 to end as temp.sas;

:.,600w temp.sas   save current line to line 600 as temp.sas;
:r /temp/data.sas  copy file data.sas in directory /temp and paste from current cursor position;

:e#      switch two open file back and forth;

:e!      return to original unsaved version;

7). global replacement:

:%s/run/jump/g     replace all run with jump;

:2,10s/jump/run/g  replace all jump from line 2 to 10 with run;

:2,10s/jump/run/gc replace all jump from line 2 to 10 with run, but need confirm;

:%s/./\U&/g        change to --> UPPERCASE;

:%s/./\L&/g        change to --> LOWERCASE;

:.,+5s/$/?/        add "?" at the end of 6 lines from current line;

:g/^/mo0           reverse the order;

:v/Paid in Full/s/$/Overdue/     if there is no "Paid in Full" on the line, will append a "   Overdue" at the end of the Line;

:%s/^/ >  /      add " >  " at the beginning of each line;

:%s/^/   /       add "   " at the beginning of each line;

:%s/$/ <  /      add " <  " at the end of each line;

:%s/^ *\(.*\)/\1/  delete all leading space of each line;

:%s/\(.*\) *$/\1/  delete all ending space of each line;

:g/^$/d            delete all blank lines;

8). advanced editing:

:set ic          search is no case sensitive;

:set noic        search is case sensitive;

:set window=50   50 lines each screen;

:!date           give date and time;

:r !date         read date to file;

:r !sort aa.sas  sort aa.sas and paste all from current cursor;

9). print: (c-tasc specific)
hp45 -z1 temp.lst

hp45 -z1 -p17 temp.lst

hp45 temp.lst

10). home/xxx/programs/subdir

grep neopt *.lst |more

11).
cp /home/xxx/programs/subdir/*.*  .

cp ../xxx/*.*  .

compress *
uncompress *

12). 
chmod 751 temp.sas: change read only mode to edit mode;
chmod 770 temp.sas: allow group members to access;
chmod g+s pgmsdir: allow directory access;

13). more memory requisition to run sas:
-memsize 256m -work /lv10/tmp temp.sas

14). 
cal --> show calendar;

spell a.sas --> show misspelled words;

df --> show free space;

who --> who is log on;

date --> show current date & time;

15). set up timer for auto run:
crontab -e
00 05 06 11 * /lv06/sas91/sas /home/xxxxx/programs/temp.sas
(close editor using :wq)

The above command will submit a sas program called temp to run at 5:00AM on November 6. The general format of the crontab command is minute hour day-of-month month weekday (Sunday is 0).

00 15 * * 1-5 /lv03/sas82/sas /home/dlxxx/somejob.sas
4 45 11 28 9 * /usr/local/sas/SASFoundation/9.3/sas /sasdata/ltrc/reports/temp.sas

16). Search for file with a specific name in a set of files (-name) 
find . -name "*conf" -print

This command will search in the current directory and all sub directories for a file ending with conf. 

Note: The -print option will print out the path of any file that is found with that name. In general -print will print out the path of any file that meets the find criteria. 

17). Use ls to create a sas file
ls . > temp.sas

This command will create a sas file temp.sas, which includes all the directory names, file names under current directory. Replace directory name with . to create file for other directory. 

18). Use diff to compare two sas files
diff program1.sas program2.sas

Positioning the SUBSTR Function

SAS uses the SUBSTR function to extract a substring or to modify a variable's values, depending on the position of the function in the assignment statement.

When the function is on the right side of an assignment statement, the function returns the requested string.

MiddleInitial=substr(middlename,1,1);

But if you place the SUBSTR function on the left side of an assignment statement, the function is used to modify variable values.

substr(region,1,3)='NNW';

When the SUBSTR function modifies variable values, the right side of the assignment statement must specify the value to place into the variable. For example, to replace the fourth and fifth characters of a variable named Test with the value 92, you write the following assignment statement:

substr(test,4,2)='92';

Test Test

S7381K2 S7392K2
S7381K7 S7392K7

Resolve Function

%let first=Steve;
%let last=Hanks;

data _null_;
      name1='Mr '||"&first"||' '||"&last";
      name2=resolve('Mr &first &last');
      put name1;
      put name2;
run;

105 data _null_;
106 name1=‘Mr ’||”&first”||’ ’||”&last”;
107 name2=resolve('Mr &first &last');
108 put name1;
109 put name2;
110 run;

Mr Steve Hanks
Mr Steve Hanks

Using %SYSFUNC Functions

%let dsid=%sysfunc(open(datamart.customer)) ;
%let nobs=%sysfunc(attrn(&dsid,nobs)) ;
%let dsid=%sysfunc(close(&dsid)) ;

%put The number of obs in datamart.customer is &nobs ;

81 %let dsid=%sysfunc(open(datamart.customer)) ;
82 %let nobs=%sysfunc(attrn(&dsid,nobs)) ;
83 %let dsid=%sysfunc(close(&dsid)) ;
84 %put The number of obs in datamart.customer is &nobs ;
The number of obs in datamart.customer is 6299497


Using %SYSFUNC For Report Date

proc print data=sashelp.class;
      title "We can put a todays (%sysfunc(today(),date9.)) date in a report title";
run;

SYMGLOBL and SYMLOCAL

NOTE: Macro variable created inside the macro do loop is local, but can be changed to global by adding %global var after %do ...

%let c=yes;

%macro test;

%let d=yes;

data _null_;
      if symlocal ('c') then put '**** c is local';
      if symglobl('c') then put '**** c is global';
      if symlocal ('d') then put '**** d is local';
      if symglobl('d') then put '**** d is global';
run;

%mend test;
%test;

**** c is global
**** d is local

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Deleting a Macro Variable

NOTE: symdel will NOT work on local macro variable.

%let a=yes;

data _null_;
      if symexist('a') then put '**** a exists';
          call symdel('a');
      if symexist('a') then put '**** a still exists';
          else put '**** a doesnt exist';
run;

**** a exists
**** a doesnt exist

NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

SYMEXIST to check if a Macro Variable Exists

%let a=1;

data _null_;
      if symexist('a') then put '**** a exists';
          Else put '**** a doesnt exist';
      if symexist('b') then put '**** b exists';
          Else put '**** b doesnt exist';
run;

**** a exists
**** b doesnt exist

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Macro IN Operator (SAS 9.2)

In SAS 9.2 version:

%macro test;
%if &dsn IN ae ds co cm %then %do;
      Some---SAS—Statements;
%end;
%test;

Note:
No need of % sign in front of IN operator.
In SAS 9.2 both IN and # work if you use the system option minoperator inside your macro call.

%macro test/minoperator;
%if &dsn # ae ds co cm %then %do;
      Some---SAS—Statements;
%end;
%test;

%macro test;
%if &dsn IN ae ds co cm %then %do;
      Some---SAS—Statements;
%end;
%test;

MINOPERATOR option tells SAS to recognize the word 'IN' or special symbol # by the SAS macro facility as an infix operator when evaluating logical or integer expressions.

Here is another way of writing the macro code with delimiters.

Use MINDELIMITER option to change the default delimiter from space to any other, in this case it is comma (,).

options mindelimiter;

%macro test/mindelimiter=',';
%if &dsn IN ae,ds,co,cm %then %do;
      Some---SAS—Statements;
%end;
%test;

CALL SYMPUTX / CALL SYMPUT

CALL SYMPUTX is similar to CALL SYMPUT except that

•CALL SYMPUTX does not write a note to the SAS log when the second argument is numeric. CALL SYMPUT, however, writes a note to the log stating that numeric values were converted to character values.

•CALL SYMPUTX uses a field width of up to 32 characters when it converts a numeric second argument to a character value. CALL SYMPUT uses a field width of up to 12 characters.

•CALL SYMPUTX left-justifies both arguments and trims trailing blanks. CALL SYMPUT does not left-justify the arguments, and trims trailing blanks from the first argument only. Leading blanks in the value of name cause an error.

•CALL SYMPUTX enables you to specify the symbol table in which to store the macro variable, whereas CALL SYMPUT does not. Symbol table is optional and the valid value of it is G, L, and F. If put G, then the macro variable will be stored in the global symbol table; else if specify L, SAS will store the macro in the local symbol table; else if not specify or specify F, SAS follows the same rules as like for Call Symput.

Remove formats/informats/labels from a SAS dataset

data demo;
      set demo;
      attrib _all_ label=''; *remove labels;
      format _all_; *remove formats;
      informat _all_; *remove informats;
run;

SASHELP / DICTIONARY

VCATALG / CATALOGS
SAS catalogs

VCOLUMN / COLUMNS
Data set columns and attributes

VEXTFL / EXTFILES
Allocated filerefs and external physical paths

VINDEX / INDEXES
Data set indexes

VMACRO / MACROS
Global and automatic macro variables

VMEMBER / MEMBERS
SAS data sets and other member types

VOPTION / OPTIONS
Current SAS System option settings

VTABLE / TABLES
SAS data sets and views

VTITLE / TITLES
Title and footnote definitions

VVIEW / VIEWS
SAS data views

CAT/CATS/CATT/CATX Functions

These Functions and Call Routines can be used to join two or more strings together.
Even though we can use the concatenation operator in combination with the STRIP, TRIM, or LEFT functions, these functions make it much easier to put strings together and if you wish, to place one or more separator characters between the strings.
One advantage of using the call routines than the functions is improved performance.
Note: *Call routine executes faster than the function… in specific…

CALL CATS:
To concatenate two or more strings, removing both leading and trailing blanks.
CATS () stands for concatenate and strip.
Just think the ‘S’ at the end of the CATS as “Strip Blanks”.
Syntax: CALL CATS (result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION= RESULT
CALL CATS(RESULT, A,B)= "ABCCONSULTING"
CALL CATS(RESULT, A,B,C)= "ABCCONSULTINGINC"
CALL CATS(RESULT, "HELLO",D)= "HELLOTEAM"

CALL CATT:
To concatenate two or more strings, removing only trailing blanks.
Just think the ‘T’ at the end of the CATT as “Trailing Blanks” or “Trim Blanks”.
Syntax: CALL CATS (result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION= RESULT
CALL CATT(RESULT, A,B) ="ABC CONSULTING"
CALL CATT(RESULT, A,B,C)= "ABC CONSULTINGINC"
CALL CATT(RESULT, "HELLO",D)= "HELLO TEAM"

CALL CATX:
To concatenate two or more strings and removing both leading and trailing blanks and places a single space, or one or more characters of our choice, between each strings.
Just think the ‘X’ at the end of the CATX as “add eXtra blank.”
Syntax: CALL CATX (separator, result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION =RESULT
CALL CATX(" ",RESULT, A,B) ="ABC CONSULTING"
CALL CATX(" ,", RESULT, A,B,C) ="ABC,CONSULTING,INC"
CALL CATX(' / ', RESULT, "HELLO",D) ="HELLO/TEAM"
CALL CATX(' *** ', RESULT, "HELLO",D) = "HELLO***TEAM"

FMTLIB

Adding the keyword FMTLIB to the PROC FORMAT statement displays a list of all the formats in your catalog, along with descriptions of their values.

libname library 'c:\sas\formats\lib';

proc format library=library fmtlib;
run;

Programming Methodology (Stanford)

Combining a Grand Total with the Original Data

*** Output grand total of sales into a data set;
Proc means data=videos;
      var sales;
      output out=summarydat sum(sales)=grandtotal;

*** Combine the grand total with the original data;
data videosummary;
      IF _N_=1 THEN SET summarydat;
      SET videos;
      percent=sales/grandtotal * 100;


Output:

sales grandtotal percent
1930 12880 14.9845
2250 12880 17.4689
...

System Options for Debugging Macro Errors

MERROR (default) | NOMERROR

When this option is on, SAS will issue a warning if you invoke a macro that SAS cannot find.

SERROR (default) | NOSERROR

When this option is on, SAS will issue a warning if you use a macro variable that SAS cannot find.

MLOGIC | NOMLOGIC (default)

When this option is on, SAS prints in your log details about the execution of macros.

MPRINT | NOMPRINT (default)

When this options is on, SAS prints in your log the standard SAS code generated by macros.

SYMBOLGEN | NOSYMBOLGEN (default)

When this options is on, SAS prints in your log the values of macro values.

Customize page numbers in RTF output

ods escapechar='^';
ods listing close;
ods rtf file='c:\tests\test.rtf';

data test;
      do i=1 to 50;
          output;
      end;
run;

proc print data=test noobs;
      title 'Page ^{thispage} of ^{lastpage}';
      footnote '^{pageof}';
run;

ods listing;
ods rtf close;

Macro Functions: %EVAL and %SYSEVALF

%EVAL function only supports integer arithmetic values. Macro statements performing integer arithmetic calculations:

%let one=%eval (3+5);
%let two=%eval (5*2);
%let three=%eval (9/3);
%let four=%eval (5/2);
%put The value of one is &one;
%put The value of two is &two;
%put The value of three is &three;
%put The value of four is & four;

Open the Log file and see the results as follows:
The value of one is 8
The value of two is 10
The value of three is 3
The value of four is 2

The value for macro variable four, should be 2.5, instead it shows only two. That happens because if we perform division on integers, integer arithmetic doesn’t take the fractional part into account.

%let last= %eval (5.0+3.0); /*INCORRECT*/

The values here in the above statement have a period character to numeric values and because of that the macro processor stops evaluating and produces the following error message: “ ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 5.0+3.0 “


Evaluating Floating Point Operands

The %SYSEVALF function can perform arithmetic calculations with operands that have the floating point values.

%let test= %sysevalf(1.0*3.0);
%let final= %sysevalf(1.5+2.8);
%let last= %sysevalf(5/3);
%put The value of test is &test;
%put The value of final is &final;
%put The value of last is &last;

The %PUT statements display the following messages in the log:
The value of test is 3
The value of final is 4.3
The value of last is 1.66666666666666

%SYSEVALF function perform arithmetic calculations and the result of the evaluation can be a floating point value like in the final and last macro variable case, but as in integer arithmetic calculations, the result is always a text.

The %SYSEVALF function be used in conjugation with other functions like, INTEGER, CEIL, and FLOOR.

For example, the following %PUT statements return 3, 4 and 3 respectively:
%let val=3.8;
%put %sysevalf(&val,integer); *Value returns in the log is 3;
%put %sysevalf(&val,ceil); *Value returns in the log is 4;
%put %sysevalf(&val,floor); *Value returns in the log is 3;


Difference between %eval and %sysevalf functions can be understand better with the following example;

%let value=9;
%let value2=5;
%let newval=%sysevalf(&value/&value2);
%let newval1=%eval(&value/&value2);
%put &newval;
%put &newval1;

*Ans: newval=1.8;
*Ans: newval1=1;

Functions: YRDIF, DATDIF, INTCK

Using YRDIF function:
“act/act” will gives us the actual interval between the two dates.
To know the interval between two dates in Years:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      years=yrdif(sdate,edate,'act/act');
      put years;
run;

Output: 10.2535 yrs


Using DATDIF function:
To know the interval between two dates in Days:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      days=datdif(sdate,edate,'act/act');
      put days;
run;

output: 3745 days


Using the INTCK function:
The INTCK function returns the integer count of the number of intervals in years, months or days between two dates.

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      years=intck(‘year’,sdate,edate);
      put years;
run;

output:10 years


To know the interval between 2 dates in days:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      days=intck(‘days’,sdate,edate);
      put days;
run;

result: 3745 days


To know the interval between 2 dates in months:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      months=intck(‘months,sdate,edate);
      put months;
run;

result: 123 months

Phonetic Matching (=*) & Pattern Matching (% and _)

Phonetic matching (Sounds-Like Operator =*)
e.g.
where custname =* 'Lafler';

Finding patterns in a string (Pattern matching % and _)
e.g.
where prodtype like '%soft%';
where prodtype like '____a%';

LIKE Clause in CREATE TABLE

The LIKE clause tiggers the existing table's structure to be copied to the new table minus any column dropped with the KEEP= or DROP= option.

proc sql;
      create table hot_products
      like products;
quit;

LOG Results:
NOTE: Table HOT_PRODUCTS created, with 0 rows and 5 columns.

Call Execute

Data step call routine
call execute('%sales');
call execute('%sales('||month||')');
The following DATA step uses CALL EXECUTE to execute a macro only if the DATA step writes at least one observation to the temporary data set.

%macro overdue;
proc print data=late;
title "Overdue Accounts As of &sysdate";
run;
%mend overdue;

data late;
set sasuser.billed end=final;
if datedue<=today()-30 then
do;
n+1;
output;
end;
if final and n then call execute('%overdue');
run;

SYSPARM

Conditionally Execute Batch Jobs

data one;
      set one;
      if scan(sysparm(),1) ^= ' ' then do;
......

data _null_;
      array p{3} p1-p3;
      inp=sysparm();
      if inp='' then abort abend;
      do i = 1 to 3;
        p[i]=scan(inp,i);
      end;
      call symput('v1',left(trim(p1)));
......


sas program.sas -sysparm "XYZ ABC OPQ"

MOD function

MOD (dividend-expression, divisor-expression)

Returns the remainder from the division of dividend-expression by divisor-expression.

Statements	Results
a=mod(10,3);	1
a=mod(.35,-.1);	0.05

Sample program: (select certain observations from a dataset)

data temp;
       set temp;
       if mod(_N_,3)=0;
run;

Options VALIDVARNAME=ANY

VALIDVARNAME=V7 V6 UPCASE ANY

V7 - (default) indicates that up to 32 mixed case alphanumeric characters are allowed. Names must begin with alphabetic characters or an underscore.

V6 - only 8 bytes long.

UPCASE - variable names are uppercased.

ANY - allows any characters to appear as valid SAS variable names. Symbols, such as "=" and "*", must be contained in a 'varname'n construct.
e.g.
libname foo ......;

data foo.'My Table'n;

input 'Amount Budgeted'n 'Amount Spent'n 'Amount Difference'n;

Calculate Age

%macro age(date,birth);

floor((intck('month',&birth,&date)
    - (day(&date) < day(&birth))) / 12);

%mend age;

Compress

Strip off some typical special characters:

test2=compress(wordvb, '0D'x);
*** remove the carriage return;

test2=compress(test2, '0A'x);
*** remove the line feed;

test2=compress(test2, 'A0'x);
*** remove non-breaking space;

varname = compress(varname, , 'kw');
*** The modifier “k” stands for ‘KEEP’ and the modifier “w” stands for ‘WRITABLE’. Note that there is no second parameter in the above code. When compress function is used in combination of K & W modifiers, it keeps all the writable characters which means it deletes all the non writable characters.

Case Logic Versus COALESCE Expression

PROC SQL;
      SELECT CUSTNAME,
        CASE
          WHEN CUSTCITY IS NOT NULL THEN CUSTCITY
          ELSE 'Unknown'
        END AS Customer_City
      FROM CUSTOMERS;
QUIT;

PROC SQL;
      SELECT CUSTNAME,
        COALESCE(CUSTCITY, 'Unknown')
        AS Customer_City
      FROM CUSTOMERS;
QUIT;

ANSI Standard

The ANSI standard reserves a number of SQL keywords from being used as column names. If a column name conflicts with a reserved word,

PROC SQL DQUOTE=ANSI;
      SELECT *
      FROM RESERVED_WORDS
      WHERE "WHERE"='EXAMPLE";
QUIT;

Using Macros to Skip a Section of Code

Just put a “%macro name” at the top and a “%mend” at the end and never call the macro. This effectively comments out the code without running it. No need to worry about other comment styles in the code.

I. Statement Can Be Repeated Or Nested

SAS statements you want to execute.....
%macro SKIP ;
      SAS statements you want to skip.....
%mend SKIP ;

SAS statements you want to execute.....

%macro SKIP ;
      More SAS statements you want to skip.....
%mend SKIP ;
SAS statements you want to execute.....

II. Use NOSOURCE Option

Options Nosource;
%macro SKIP ;
      SAS statements you want to skip.....
%mend SKIP ;

Options Source;
SAS statements you want to execute.....