Listing all files that are located in a specific directory (update)

I prefer the codes independent on OS. It may save a lot of time to maintain. Based on SAS Note 25074, I have created the codes at below:

%macro readdir(indir=, outdsn=);
    data &outdsn (keep=name infoname infoval);
       rc=filename("mydir", "&indir");
       did=dopen("mydir");

        if did > 0 then do;
            memcnt = dnum(did);
            do i=1 to memcnt;
                name = dread(did, i);
                
                rc = filename("myfile", catx("/", "&indir", name));
                fid = fopen("myfile");
                if fid > 0 then do;
                    infonum=foptnum(fid);
                    do j=1 to infonum;
                        infoname=foptname(fid, j);
                        infoval=finfo(fid, infoname);
                        output;
                    end;
                end;
                else do;
                    msg = sysmsg();
                    put msg;
                end;
                rc = fclose(fid);
            end;
        end;
        else do;
            msg=sysmsg();
            put msg;
        end;

        rc = dclose(did);
        rc = filename("mydir");
    run;
%mend;

%readdir(indir=C:\test, outdsn=out)

How to read CSV using PROC IMPORT

I believe you have read a lot paper for how to read CSV file. Here I want to add some helpful tips:
#1.
We can control the TERMSTR, ENCODING and LRECL using fileref, which may affect how PROC IMPORT handle the file.
For example, we can read UNIX format file in Win SAS.

filename in "&incsvfile" termstr=lf encoding=utf8 lrecl=32767;
proc cimport in=in out=work.test dbms=csv; 
    getnames = yes;
    datarow = 2;
    guessingrows = 2147483647;
run;

#2.
CSV file may not be read correctly when null value is using two double-quote. To fix this, please set the macro variable EFI_NOQUOTED_DELIMITER as follows:

%let EFI_NOQUOTED_DELIMITER = yes;

#3.
I always add the option GUESSINGROWS with maximum value to make sure all data are read. The option does not exist when I blog first CSV tip on 2009. :)

guessingrows = 2147483647;

Calculating leap year

SAS support give out one way to identify leap year:
Sample 44233: Calculating leap year using PROC FCMP and user-defined function

Below is another simple method:

/* check if Febuary has 28 or 29 days in the year */
data _null_;
 do year = 1997 to 2005;
  days = datdif(mdy(2,1,year), mdy(3,1,year), 'act/act');

  if days=29 then leap='Yes';
  else leap='No';

  put year= leap=;
 end;
run;

Search at one click

I always search something as follows:
1. go to http://support.sas.com/
2. input keyword in blank search box, e.g. proc format
3. select Documentation in "Search support.sas.com"
4. Click search button

It becomes boring when I always do the same steps.

Here is a tip:
1. install CleverKeys for Windows at http://www.cleverkeys.com/ck.html?p=home&os=windows
2. Right click CleverKeys -> Preference -> Web links -> Add new link at below
Description: SAS Document
URL before: http://support.sas.com/dsearch?Find=Search&ct=5210&qt=
URL after: &col=suppprd&nh=10&qp=&qc=suppsas&ws=1&qm=1&st=1&lk=1&rf=0&oq=&rq=0

Of course, you can add any other useful search engines.
My favorite links are Wikipedia, Google and SAS Document.

Now you can search the SAS at one click. :)

Special permanent libraries: USER/LIBRARY

We all know that SAS has two kinds of library: permanent library and temporary WORK library. However, do you know that there are still two special permanent libraries?

USER:
When it exists, all datasets reference with a one level name will be written to the the permanent USER library instead of the temporary WORK library.

For the convenience of debugging, I suggest we can miss "WORK." when reference dataset.

Note: system option USER has the same effect.

LIBRARY:
In FMTSEARCH, there are ALWAYS two implicit format catalogs: WORK.FORMATS and LIBRARY.FORMATS. If they do not appear in FMTSEARCH= list, they will be searched at first.

Regex search in enhanced editor

Every one has its own habit.

I always edit text in Ultraedit. I like the Regex search/replace functionality in Ultraedit so much as I maintain a list of common Ultraedit-style Regex string.

I know that SAS enhanced editor must have this functionality. However, I got no information for Regex grammar from SAS online doc.

Today, I find the Regex magic box incidentally. :)

NOTE: MERGE statement has more than one data set with repeats of BY values

First, the message reminds the user that it is MANY-to-MANY merge in DATA step.

Most SAS progammers are used to ONE-to-ONE or ONE-to-MANY merge since it is easy to understand. When MANY-to-MANY merge occur, the programmer should pay more attention on the data itself.

To perform MANY-to-MANY merge, there are two popular SAS techniques: DATA step and PROC SQL. It should be noted that PROC SQL do not issue the reminder message in MANY-to-MANY merge. Furthermore, it will create a different dataset with the result of DATA step.

Therefore, I suggest that we should perform merge using DATA step.

Scheduler for asynchronous script processing

As a IT programmer, we have to handle scripts on many platforms, e.g. DOS, ksh.
Due to limited functionality of script language, it is not easy to schedule many scripts to let them run step by step.

With SAS, we can take it easily.
I strongly recommended SAS programmer should use SYSTASK and WAITFOR to control the script processing.
By contrast, X is suitable for interactive task.

Below are codes from SAS online doc:

systask command "sas myprog1.sas" taskname=sas1;
systask command "sas myprog2.sas" taskname=sas2;
systask command "sas myprog3.sas" taskname=sas3;
waitfor _all_ sas1 sas2 sas3;

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.

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;

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

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.

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;

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/.

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