Automatic index variable _i_ in DO OVER

Recently SAS-L started an new topic "Do over can't be nested?".
I think it is very helpful for anyone who is interested with DO OVER.

quote:
Paul Choate says:
A little history lesson - in the 5.18 manual page 44 chapter 4 it says: "You can process more than one array in a DO OVER group if the arrays have the same number of elements and the same index variable."

It later says: "... (DO OVER) arrays use the automatic index variable _I_."
unquote

* Sample code;
data _null_;
    array arra a1-a10 (0 1 2 3 4 5 6 7 8 9);
    array arrb b1-b10 (10 11 12 13 14 15 16 17 18 19);

    _i_=3;
    put _i_= arra= arrb=;

    _i_=6;
    put _i_= arra= arrb=;

    do over arra;
        put "Start: " _i_=; * _i_ will be initialized to 1;
        do over arrb;
            put _i_= arra= arrb=;
        end;
        put "End: " _i_=; * ;
    end;
run; 

RUN-group processing in SAS/Graph procedures

In SAS packages, some procedures may support RUN-group processing, e.g. Proc CATALOG, Proc DATASETS in SAS/Base, Proc GPLOT, Proc GCHART in SAS/Graph and Proc REG in SAS/STAT, etc.

Basically, RUN-group processing is used to perform task interactivly and efficiently without restarting the procedure. It is very intersting that ONLY the ones in SAS/Graph can reset or cancel global and local statements, BY statements, WHERE statement.

* Sample Code;
proc sort data=sashelp.class out=class;
 by name;
run;
proc gplot data=class;
 where;
 by;
 title "All Subjects";
 plot height*weight;
run;
 where sex='M';
 by name;
 title "Male";
 plot height*weight;
run;
 where sex='F';
 by;
 title "Female";
 plot height*weight;
run;
quit;

For more information, please read SAS/GRAPH Processing: RUN-Group Processing.

Read and sort in one step - Hash object tip

In SAS world, the classical way to process raw file is to read it into SAS dataset and then sort it. With SAS9 Hash object in DATA step, we can make an all-in-one step.

* Hash sample - read and sort raw data;
data _null_;   
   /* Declare hash object and key and data variable names */
   if _N_ = 1 then do;
      declare hash h(ordered:'A');
      rc = h.defineKey('key');
      rc = h.defineData('key','value');
      rc = h.defineDone();
   end;

   /* Read key and data values */
   infile datalines eof=eof;
   input key value $;

   /* Add key and data values to hash object */
   rc = h.add();

   return;
   eof: rc = h.output(dataset:"ordered");
cards;
1 value1
3 value2
2 value3
;

Actually, it is only one little tip of Hash object.
We must never forget that the main functionality of Hash object is to quickly and efficiently store, search, and retrieve data based on lookup keys.

To list ALL using PRELOADFMT

The option PRELOADFMT is available only with Proc MEANS, Proc REPORT and Proc TABULATE.
To include all ranges and values of the user-defined formats in the output, the 3 Procs give out different usages.
Below are the samples for that.

proc format;
value $sex
    'M' = 'Male' 'F' = 'Female' 'O' = 'Other';
run;

proc means data=sashelp.class completetypes mean;
    format sex $sex.;
    class sex / preloadfmt;
    var height;
run;

proc report data=sashelp.class completerows nowd;
    format sex $sex.;
    column sex height;
    define sex / group preloadfmt;
    define height / analysis mean;
run;

proc tabulate data=sashelp.class ;
    format sex $sex.;
    class sex / preloadfmt;
    var height;
    tables sex*height*mean / printmiss;
run;

Logical expression play differently between %eval and DATA step

When programming macro, It is helpful to complete it in a DATA step and then port the code to macro. However, not all tricks in DATA step work in macro world.

Below is one tip for that:

data _null_;
    do a=1 to 4;
        if 1 < a < 3 then put a= "is between 1 and 3";
    end;
run;

%macro test();
    %put Wrong example:;
    %do a=1 %to 4;
        %if %eval(1 < &a < 3) %then 
            %put a=&a is between 1 and 3;
    %end;

    %put Correct example:;
    %do a=1 %to 4;
        %if %eval(1 < &a and &a < 3) %then 
            %put a=&a is between 1 and 3;
    %end;
%mend;

%test()

Get nth value of a variable

It is another idea to get nth value of a variable.

proc sort data=sashelp.class out=tmp(keep=weight) 
     nodupkey;
  by descending weight;
run;

data _null_;
  set tmp end=end;

  retain weight_10th .;
  if _n_ <= 10 then do;
    weight_10th = weight;
  end;

  if end then put "The 10th weight is " weight_10th=;
run;

undocumented routine: CALL SOUND

I always keep an eye closely on SAS Samples, where I can get many interesting tips.

From this new sample, we can infer that CALL SOUND is another undocumented routine.
It will be a great way for reminder, not just in EG.

CALL SORTx routines

With CALL SORTx routines, we can sort and EXCHANGE the values of the variables. It is a very helpful since we need not assign a temporary variable for exchange of the values.
Below is an interesting sample code:

* Get the first two max height values;
data _null_;
  set sashelp.class end=last;
  retain max1 max2;

  call sortn(height, max2, max1);
  if last then put "max height value: " max1 max2;
run; 

ANYDT* informats: Key to date/time values

Before SAS 9, we have to handle date/time values conditionaly if they are not in uniform style. Now, it becomes easy to input many different variations of date/time values using ANYDT* informats.
It is mainly used in PROC IMPORT, the Import Wizard, raw file input which have different styles of date/time values.

As to ANYDT* informats, notice the two points at below:
#1: There is no INVALID DATA message or set _ERROR_ to 1 if the input text cannot be interpreted. (SAS Note)
#2: A new system option DATESTYLE is introduced to determine the sequence of month, day, and year

Check variable exist in DATA step run time

First off, there is NO way to know exactly which variable exist in DATA step run time.

We can check the variable existence before DATA step run time. The popular method is Macro technology with File I/O functions or Dictionary table.

How to avoid creating this variable if it does not exist and to get the formatted value of the variable if it exist.
Here ia another way:

* Sample;
data dummy;
 set sashelp.class (obs=0);
run;

data test;
  set sashelp.class;

  if _n_ = 1 then do;
    drop varexist dsid rc;
    retain varexist;
    dsid = open('dummy');
    varexist = varnum(dsid, "name");
    rc = close(dsid);
  end;

  length tmp $ 20;
  if varexist then do;
    tmp = vvaluex("name");
  end;
  else tmp = '';
run;

PUT function vs. INPUT function

What about the relationship between them?

In my opinion, INPUT function is the inverse process of PUT function.
PUT function: convert the variable value to a character string
INPUT function: convert a character string to the variable value

Macro variable in PROC SQL

There are two ways to store values of column in PROC SQL.
1. mv with separator, which store values of the rows into one mv
2. mv range, which store value of one row per mv

* Sample code;
proc sql;
   select distinct sex, name 
   into:sex seperated by ' ', :name1-:name100
   from sashelp.class;

   %let num = &sqlobs;
quit;

%put Number: &sqlobs;
%put _global_;

Short for style expression

With the power of ODS, we can specify the style elements for specific location, especially for reporting procedure like PROC PRINT, PROC REPORT, PROC TABULATE.
If the style expression is trivial and long, it will be boring to apply same style expression to many reports at one time.

For example:

ods html file="style.html";
proc report data=sashelp.class nowd;
  column name age;
  define name/display 
              style(header)=[just=l 
                        foreground=red 
                        cellwidth=2cm];
  define age / display ;
run;
ods html close;

To avoid typing the same expression again and again, we can customize style using PROC TEMPLATE.

The report above can be converted to:

proc template;
 define style styles.mystyle;
   parent=default;
   style header1 from header /
     foreground=red
     width=2cm
     textalign=left;
 end;
run;

ods html file="style2.html" style=mystyle;
proc report data=sashelp.class nowd;
 column name age;
 define name / style(header) = header1;
 define age / display ;
run;

ods html close;

Are you missing "missing option"?

Generally, SAS do not take missing value as a valid value, especially for class variable. That means there will be an implicit filter to delete all "missing" class, e.g. the class variable in Proc MEANS, PROC FREQ.

Likewise, missing values is not valid value for any group, order or across variable in Proc REPORT.

What ODS templates are in use?

Generally, procedure output will use table template and style template.

Below are the methods to identify them:
#1:
ODS TRACE statement can print what table template is in use.

#2:
Default style information is in SAS registry.

command "regedit" -> ODS -> DESTINATION -> "Selected Style" entry in specified destination

Close many VIEWTABLES at one time

In SAS DMS, I always view the dataset using Viewtable.
It is boring to close many viewtables before rerun the pgm.

I ever asked for help on comp.soft-sys.sas newsgroup. However, I am not satisfied with the answers.
Today, an idea rushed my mind. I have tried and believe it is a wonderful tip. :)

Here are the steps:
1. Define command style macro %closevts and save as closevts.sas

%macro closevts / cmd;
  %local i;
  %do i=1 %to 20;
  next "viewtable:";end;
  %end;
%mend;

2. Update configuration file

-cmdmac
-set sasautos (
.......
macro-%closevts-path
)

3. Issue %closevts in command line

%LOCAL secrete

When programming, we always put the %LOCAL and %GLOBAL statement at the top of macro definition.

Why?
In this way, we can guarant that same LOCAL/GLOBAL mv is always in effect in macro execution. If %LOCAL is in the middle, the global mv is probably in use.

*%LOCAL secrete sample;
%let test = global;
%macro test();
  %put Before %nrstr(%local): "&test";

  %local test;
  %put After %nrstr(%local): "&test";
%mend;

%test()

ID functionality in PROC MEANS

In real world, there are two kinds of data: numerical and categorical.
Regardingly, in SAS world there are two basic tools: PROC MEANS and PROC FREQ.

PROC MEANS is commonly used to summarize numerical data.
Additionaly, it can provide ID functionality as follows:

1. ID statement + IDMIN and PRINTIDVARS options in PROC statement
2. MAXID and MINID options in OUTPUT statement
3. IDGROUP option in OUTPUT statement

All work only for output dataset except PRINTIDVARS, which is for the printed output.
As far as output dataset is concerned, the ID functionality scope is #1 < #2 < #3.
That means IDGROUP is most powerful, like TopN/BottomN, obs, last etc.

Correspondingly, IDGROUP is most complex.

How to position the macro problem at runtime

Macro programming is error-prone. It is easy to debug the Macro compile error.
However, it is not intuitive to debug Macro runtime error since we can not get correct postion information from SAS log.

For example:

%macro test;
    data a;
        a=1;
        b="1";
        if a=b then put "Here!";
    run;
%mend;

%test

SAS log:
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
1:51
Although SAS log give out the postion "1:51", we can not trace the issue in Macro.

Here is one key to the issue. We can save the Macro output as SAS program, position the issue in SAS program and track back to Macro.
Please see the sample code at below:

filename mprint temp;
options mprint mfile;

%test

%include mprint / source2;
options nomprint nomfile;
filename mprint clear;

CSV with newline

In CSV, fields with embedded newline must be enclosed within double-quote characters.
However, PROC CIMPORT fail to import this kind of CSV.

To conform to the input standard, we can convert the embedded newline into " \par " (see RTF specification), import CSV in SAS dataset using PROC CIMPORT and then convert " \par " back to newline.

Dataset File-Handling statements - SET/MERGE/UPDATE/MODIFY

Totally there are 4 dataset file-handling statements.
All can support the fomula as follows:

DATA DS;
SET DS1 DS2; *SET can be replaced with MERGE/UPDATE/MODIFY;
BY VAR;
RUN;

However, they have different meaning.

SET statement: interleave two or more SAS datasets. We can get an ordered dataset without PROC SORT.
Merge statement: match-merging, which is very popular.
Update statement: update Master dataset with Transaction one, which is the only formula. Before using it, please read carefully the requirements in manual.
Modify statement: update Master dataset in place using matching access method. Although it is a powerful tool, many programmer would like to bypass it using very complicated code.

Truncate issue when importing CSV file

CSV file is an old common format of information distribution. However, when we read CSV into SAS dataset using PROC IMPORT, the string is truncated sometimes.
The reason is that PROC IMPORT scan only 20 records by default to determine variable attributes. SAS Notes has detailed the steps to solve the issue.

For more information, please see http://support.sas.com/kb/1/075.html.

*Sample code to read CSV;
PROC IMPORT OUT= WORK.DATA 
            DATAFILE= "csv.txt" 
            DBMS=DLM REPLACE;
     DELIMITER='2C'x; 
     GETNAMES=YES;
     DATAROW=2; 
RUN;

How to avoid "retain" observations in Match-Merging

There are two merging techniques: One-to-One Merging and Match-Merging.
Match-Merging is the most popular operation in SAS DATA step programming.
The reason is that Match-Merging always “retain” last observation until new one is read into PDV.
(Note that One-to-One merging is not.)

However, sometimes we need not retain last observation.
Since SAS do not provide option to control it, we have to initialize PDV manually every time.

Here is the code:

OUTPUT; * save PDV to dataset;
ARRAY CHAR _CHARACTER_ ;
ARRAY NUM _NUMERIC_ ;
DO OVER CHAR ; CHAR = '' ; END ;
DO OVER NUM ; NUM = . ; END ;


or

*For SAS 9.1 and later;
OUTPUT;
CALL MISSING(OF _ALL_);

Data Set List - A close friend to Set/Merge statement

Are you boring with writing a long string to concatenate/merge many datasets? With the introduction of Data Set Lists in SAS 9.2, it is “gone with the wind”.

Data set lists can give out the list of existing datasets efficiently and flexibly.
Just like variable list, it also has two formulas:
Name prefix lists: SALE1:
Numbered range lists: sales1-sales4

What is _IORC_?

It is short for Input-Output Return code. The definition is at below from SAS online doc:
The automatic variable _IORC_ contains the return code for each I/O operation that the MODIFY statement attempts to perform.

However, it is not quite right. We should get some points for _IORC_:
#1: it is automatic variable, which means it exist in PDV but do not output.
#2: it used with indexed dataset to check whether the direct read found a matching obervation. That means it do make sense in dataset with key= option, no matter what statement is used, e.g. SET statement, MODIFY statement, etc.
#3: it is a good habit to always check _IORC_ after reading indexed dataset. Here is a good example: http://support.sas.com/kb/26/002.html.
#4: since SET statement retain all variable values, we should STOP or re-assign all variables to missing and _ERROR_ to 0 when _IORC_ is not _SOK (no matching observation).

conversion from SAS environment variable to library implicitly

It is interesting that a SAS environment variable can be used as a SAS library.
The library should be assigned implicitly.

*sample code;
options noxwait;
options set=test="c:\";
libname test clear; *de-assign the library;
libname test list; *the library appear again;

*In this way, we can define user-defined formats at SAS invocation;
-set fmtlib "format-path"
-fmtsearch (work fmtlib)

Wonderful paper: Don't Be a SAS Dinosaur

As a SAS programmer, I strongly recommend that you should read this paper.
Warren Repole has summarized many SAS tips systematically.

For more information, please read http://www.repole.com/dinosaur/.

WOW, FCMP is comming

In the past, we have to use macro or format trick for user defined function.
The macro implementation is lengthy and error-prone.
Furthermore, due to inefficiency of communication between macro facility and others, the performance is bad.

With the release of SAS V92, we can take advantage of PROC FCMP.
I believe many SAS professionals have been expecting this for a long time.

It is definitely the most useful upgrade in BASE for SAS 9.2.

LOCF by using only PROC SORT

LOCF is short for Last Observation Carried Forward. It is a common method in longitudinal studies. And there are many ways to handle this. Here is one tip of using NODUPKEY option.

/* Classical way */
proc sort data=inds; 
    by subjid DESCENDING visitnum; 
run;
data locf;
    set inds (where=(visitnum <=3));
    by subjid visitnum;

    retain base;
    if first.subjid then base=.;
    if not missing(value) then base=value;
    if last.subjid;
run;

/* Use NODUPKEY option in PROC SORT. */
proc sort data=inds; 
    by subjid DESCENDING visitnum; 
run;
proc sort data=inds(where=(visitnum <=3 and not missing(value))) out=locf NODUPKEY;
    by subjid;
run;

Useful keyboard macros

As a programmer, I always use Ultraedit and VI as editor. So I am familiar with the keyboard shortcut in Ultraedit and VI. After immigrating some useful shortcuts into SAS enhanced editor using keyboard macros, my coding efficiency has been improved dramatically.
It should be noted to avoid shortcut conflict with old ones before assign new shortcut key.

Here are two useful keyboard macros:
Mark matching DO/END:
* Move cursor to matching DO/END keyword
* Mark the current line
* Move cursor to matching DO/END keyword
* Mark the current line
* Move cursor to matching DO/END keyword

Unmark matching DO/END:
* Move cursor to matching DO/END keyword
* Unmark the current line
* Move cursor to matching DO/END keyword
* Unmark the current line
* Move cursor to matching DO/END keyword

Below is a paper for keyboard macros in SAS enhanced editor.
http://www.sascommunity.org/wiki/Tip:_Useful_Enhanced_Editor_macros

macro invocation to CALL routines

In macro world, we have to use %sysfunc to call SAS functions.
Accordingly, %syscall is the door to SAS CALL routines.

%let pattern = %sysfunc(prxparse(/test/i));
%let rc = %sysfunc(prxmatch(&pattern, %str(this is a test)));
%syscall prxfree(pattern);

Keep quotation in string

Have you ever needed different quotation in a string?
If it is a trouble for you, try the code below:

*Macro variable assignment;
%let str = %str(quotation %" and %'test);
%put %superq(str);

*Macro variable reference;
%let mv = %bquote(&str);
%put %superq(mv);

*Data Step;
data _null_;
  str = 'quotation " and '' test';
  put str=;
  str = "quotation "" and ' test";
  put str=;
run;

Keyboard shortcut for "Collapse All" and "Expand All"

Are you bored with the mouse click.

Try the keyboard shortcut to save time.
Collapse All: Ctrl+Alt+"-"
Expand All: Ctrl+Alt+"+"
Collapse one node: Alt+"-"
Expand one node: Alt+"+"

count word number using PRX

It is my way to count word number:

data _null_;
   input;
   line = prxchange('s/\b*(\w+)\b*/A/', -1, _infile_);
   count = count(line, 'A');
   put count=;
cards;
this is a test
;

++++++++++++++++++++++++++++++++++++++++++++++++

Apparently, the method above is outdated.
With SAS 9.2, we can count the words in a string easilier.
The functions countw do the trick.

PRX modifier "i" and "?"

Not all perl regular expressions are supported by PRX functions.

However, it does support two useful modifers: "i" and "?"
case-insensitive modifer "i":
'/substring/i'

parens non-capturing modifer "?":
'/(?:substring1)/'

And they can be used together:
'/(?i:substring)/'
'/(?-i:substring)/'
'/(?-i:substring1)substring2/i'

functions for substring searching

Generally, we can use 4 classes of search substring:
ANY-function, NOT-function (anyalpha)
INDEX (INDEXC, INDEXW)
FIND
PRXMATCH


The function family of any-function and not-function are for search of a character string. And they are for special purpose.
Sometimes, the function COMPRESS can used in practical application alternatively.

The power of subsring searching is
INDEX < FIND < PRXMATCH

And certainly, the complexity of usage
INDEX < FIND < PRXMATCH

I prefer PRXMATCH since it has most flexibility. Although it will take long time to learn, it is worth the effort.

defensive programming when using multiple INPUTs

When there are more than one INPUT statements in DATA step, we should care about end-of-file issue. Before INPUT, we should check the enf-of-file to avoid DATA step execution stop.

It should be something like below:

infile in end=end;  *or eof= option;
input;
* something here;
if not end then do;
    input;
    * something here ;
end;
* something here;

if end then do;
    * something here;
end;

special numeric missing value

Generally, we judge the numeric missing value with "varname = .".
However, it is not good SAS habit because it may miss special numeric missing value.

From online doc, we can get the definition of Special numeric missing value:
is a type of numeric missing value that enables you to represent different categories of missing data by using the letters A-Z or an underscore.

That means there may be at most 28 numeric missing values as follows:
._
.
.A-.Z

To avoid unexpected missing, we should use MISSING and NMISS for convenience.

For more explanation, we can read SUGI31 paper: MISSING! - Understanding and Making the Most of Missing Data

How to check format exist?

The popular method to identify the format is using dictionary tables (catalogs and formats).
What is the difference between them?
If you check the existence only from the format searchpath, dictionary.formats is the key. Otherwise, you can get ALL formats using dictionary.catalogs.

proc sql;
select libname, memname, objname
from dictionary.catalogs
where objtype contains 'FORMAT' and objname = 'FMTNAME' ;

proc sql;
select libname, memname, objname
from dictionary.formats
where objname = 'FMTNAME' ;
quit;

a special function for %sysfunc world: filename function

Below are the code from SAS online doc:
%let filrf=myfile;
%let rc=%sysfunc(filename(filrf, physical-filename));
%if &rc ne 0 %then
%put %sysfunc(sysmsg());
%let rc=%sysfunc(filename(filrf));

It should be noted that the fileref is "myfile", not "filerf". That means the macro variable "filerf" contains the ACTUAL fileref.
When the macro variable does not exist, the system generates a fileref automatically.

It seems that it is unique to filename function.
For now, I have not found any other functions which can take the similar behavior.

Note that it will not work if the fileref has been assigned. For more information, please see http://support.sas.com/kb/6/567.html

macro autocall

Here are sample codes for macro autocall.
Personally, I perfer "fileref" type because it is flexible.

/* fileref */
filename utility catalog "lib.catalog";
*filename utility "c:\utility";
options mautosource mrecall sasautos=(sasautos utility);

/* libref */
libname utility "c:\utility";
options mautosource mrecall sasautos=(sasautos utility);

/* host-specific path;*/
options mautosource mrecall sasautos=(sasautos "c:\utility");

By-Group value in title statement

By default, by-group values (#byvar, #byval and #byline) in the title statement can not change among by-groups. It is reasonable since the by-group section in the procedure output should be regarded as a whole. And title statement is for the whole output, not every by-group section.

Online doc says below
"Use NOBYLINE to suppress the automatic printing of BY lines in procedure output. You can then use #BYVAL, #BYVAR, or #BYLINE to display BYLINE information in a TITLE statement."

That means the new procedure output with NOBYLINE will be splitted into several by-group sections, and every section is regarded as a whole seperately. In this way, the by-group section should have its own title.

Sample code:

proc sort data=sashelp.class out=class;
    by sex;
run;

options nobyline; *trick here;
ods html file="test.htm";
proc print data=class;
    title "Subject list - #byval1";
    var name sex height weight;
    by sex;
run;
ods html close;

However, the trick is not necessary for graph output since every graph output is a whole actually. It should be noted that goption HBY=0 can also suppress byline in graph output.

Sample code:

options byline;
goptions hby=0;
proc gplot data=class;
    by sex;
    plot height*weight;
run;

For more control for byline style, please see http://support.sas.com/kb/23/325.html

CALL EXECUTE change the "macro execution path"

As for CALL EXECUTE, SAS Manual says that
"If argument resolves to a macro invocation, the macro executes immediately and DATA step execution pauses while the macro executes. If argument resolves to a SAS statement or if execution of the macro generates SAS statements, the statement(s) execute after the end of the DATA step that contains the CALL EXECUTE routine."

And it is interesting that the macro execution path are different for CALL EXECUTE type and standard type.

Standard type executes Macro language (statement, function, variable) and SAS statements step by step. However, CALL EXECUTE type do NOT execute SAS statement, which are created by macro, but put them after the end of that DATA step.

Sample code:

%macro test(a);
%let b=2;
%put 1> a is "&a" and b is "&b";
data _null_;
    put "2> a is '&a' and b is '&b'";
    call symput('b', '3');
run;

%put 3> a is "&a" and b is "&b";
%mend;

* Standard Type;
%test(1)

* Output:
1> a is "1" and b is "2"
2> a is '1' and b is '2'
3> a is "1" and b is "3"
;


* CALL EXECUTE Type;
data _null_;
    call execute('%test(1)');
run;

* Output:
1> a is "1" and b is "2"
3> a is "1" and b is "2"
2> a is '1' and b is '2'
;