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