Get Funky with %SYSFUNC

(1)

Experts in SAS Business Intelligence

Amadeus Software Limited David Shannon

(2)

Topics to cover

Introduction

About %SYSFUNC

Six Examples

1. Check the existence of a SAS data set 2. Check an external file exists

3. Read the contents of a directory 4. Storing and restoring global options 5. Writing macro functions

6. Formatting macro variables

%QSYSFUNC

Formatting macro variables

(3)

Introduction

What are %SYSFUNC and %QSYSFUNC?

Two powerful macro functions

Enable use of most of Base SAS and SAS Component Language (SCL) functions

Formatting macro variables

%SYSFUNC vs. Data Step

SCL functions are also available through the data step environment

Data step has faster processing time than macro Data step is limited by step boundaries

(4)

About %SYSFUNC

%SYSFUNC is a Macro function that executes SAS functions

Can reference Base SAS functions, SCL functions or custom made functions created with Proc FCMP

Syntax:

%SYSFUNC( data-step-function(arguments) )

%SYSFUNC( data-step-function(arguments) , format) %QSYSFUNC( data-step-function(arguments) <,format>)

(5)

About %SYSFUNC

All data step functions except:

Note: use INPUTC, INPUTN, PUTC and PUTN for character or numeric formats

SYMGET RESOLVE PUT MISSING LEXPERM LEXPERK LEXCOMBI LEXCOMB LBOUND LAG INPUT IORCMSG HBOUND DIM DIF ALLPERM ALLCOMB All variable information functions

(6)

About %SYSFUNC

Some SAS functions:

Note: functions as per SAS 9.2 online documentation

VARTYPE VARNUM VARNAME VARLEN VARLABEL VARINFMT VARFMT SYSRC SYSMSG SYSGET SPEDIS REWIND POINT PATHNAME OPEN NOTE MOPEN LIBREF LIBNAME GETVARN GETVARC GETOPTION FWRITE FSEP FRLEN FREWIND FREAD FPUT FPOS FPOINT OPTNUM FOPTNMAE FOPEN FNOTE FINFO FILEREF FILEEXIST FGET FEXIST FETCHOBS FETCH FDELETE FCOL FCLOSE FAPPEDN EXIST DSNAME DROPNOTE DREAD DOPTNUM DOPTNAME DOPEN DNUM DINFO DCLOSE CUROBS CLOSE CEXIST ATTRN ATTRC

(7)

1. Check a Data Set Exists

Uses the EXIST() data step function

%let exist=%sysfunc(exist(sashelp.shoes));

The exist function will return:

1 if the SAS data set exists 0 if it does not exist

To retrieve the value of the exist variable use macro notation (&exist)

(8)

1. Check a Data Set Exists

Checking the existence of a data set and writing to the log window:

%macro dataset_exist;

%let exist=%sysfunc(exist(sashelp.shoes));

%if &exist=0 %then %put Data set does not exist; %else %if &exist=1 %then %put Data set exists!;

%mend;

(9)

2. Check an External File Exists

Uses the FILEEXIST() data step function:

%let rc=%sysfunc(fileexist(c:\elena\data\10Feb2004.csv));

And the fileexist function will return:

1 it the CSV file exists 0 if it doesn’t exist

(10)

2. Check an External File Exists

Checking the existence of a file before deleting

%macro delete_file;

%let rc=%sysfunc(fileexist(c:\elena\data\DATA10Feb2004.csv));

%if &rc=1 %then %do;

%let rc1=%sysfunc(filename(rawfile,

c:\elena\data\DATA10Feb2004.csv));

%let rc2=%sysfunc(fdelete(&rawfile));

%if &rc2=0 %then %let message=File was deleted;

%else %let message=File could not be deleted;

%end;

%else %let message=File does not exist;

%put &message; %mend;

%delete_file;

(11)

3. Read the Contents of a Directory

Import all CSV files found under a network directory %macro read_files;

%let rc=%sysfunc(filename(rawdata,c:\elena\data));

%let did=%sysfunc(dopen(&rawdata));

%let dnum=%sysfunc(dnum(&did));

%do i=1 %to &dnum;

%let name_file=%sysfunc(dread(&did,&i));

%put file=&name_file;

%let csv=%sysfunc(scan(&name_file,2,.));

%if &csv=csv %then %do;

%let name_file_short=%sysfunc(scan(&name_file,1,.));

%put file=&name_file_short;

PROC IMPORT OUT= WORK.&name_file_short DATAFILE=

"C:\Elena\data\&name_file" DBMS=CSV REPLACE; RUN; %end; %end; %mend; %read_files;

(12)

4. Storing & Restoring Global Options

Useful in generic, validated macros Use GETOPTION() data step function

(13)

5. Writing Macro Functions

Use %SYSFUNC to return a value

Lets write a function to validate a SAS name Use IFC function…

%let name = new_column_name; %sysfunc( ifc( %sysfunc(nvalid(&name.)) , &name. , %sysfunc(nliteral(&name.)) ) );

(14)

5. Writing Macro Functions

No

(15)

6. Formatting Macro Variables

When formatting macro variables SYSFUNC

can reduce the number of steps required

Include the date at the start of a title using

data step processing:

data _null_;

fdate=put(date(),worddate.); call symputx('wdate',fdate);

run;

(16)

Using SYSFUNC:

title "%sysfunc(date(),worddate.) Report title";

Include the date at the end of a title

(17)

Remove the leading blank spaces using the left function:

title "Report title

%sysfunc(left(%sysfunc(date(),worddate.)))";

But if we execute the code we obtain:

ERROR: The function LEFT referenced by the %SYSFUNC or %QSYSFUNC macro function has too many arguments.

Step by step processing:

title "Report title %sysfunc(left(%sysfunc(date(),worddate.)))";

title "Report title %sysfunc(left( May 6, 2009))"; First argument

of the left function

Second argument

(18)

%QSYSFUNC

Solution: Use %QSYSFUNC quoting function Same as SYSFUNC but it can mask special

characters, including & and %, and mnemonic operators

title "Report title

(19)

Conclusions

%SYSFUNC – Funky or not?

+ Alternative to data step processing with SCL

functions

+ SYSFUNC and SCL can increase the number of

functions available to a SAS programmer in the macro environment

+ Very powerful formatting tool

- Processing time can be longer compared to

(20)

Elena Muriel

Amadeus Software Ltd. Tel: +44 (0)1993 878287

Thank you for listening!

David Shannon

Amadeus Software Ltd. Tel: +44 (0)1993 878287