Friday, July 17, 2015

Dynamically assign fileref to external file

Sometimes, you might want to assign fileref to external file which path you get to know during some complex data manipulations. In that case, filename statement is difficult to use as filepath you need to know in advance. Along with FILENAME statement, SAS offers also FILENAME function, which you can use within the DATA step.

SAS Base

SAS Data Integration Studio

Let's assume that you already have SAS dataset created that contains path to csv file. Then you need to:
  • add "User Written Code" transformation (under "Data->User Written Code"),
  • connect SAS dataset (that contains path to csv file) to "User Written Code transformation".
  • Go to "User Written Code" properties, tab "Code" (keep "Code generation mode: User Written Body"), and in the code body write the following text right above %rcSet(&syserr... (it is sometimes hard to see, but automatically generated code has a greyish background, and your code has complete white background):
        data null; 
          set &_INPUT.; 
  • now create a new "External File", set metadata you want (I assume you know what you expect as a format of the csv file), and
  • under "External File"s Properties, tab "File Location", under "File name:", write simply "fid" (without quotes), and under "File Name Quoting" on the same tab, choose "No quotes around file name".

Redirect libref to WORK library in SAS

Imagine you have a SAS Enterprise Guide Project, where you have been using a libref (let's name it "SAVE") very often. However, now you want to switch it in specific occassion turn to WORK library so that it does not require a specific folder for SAS datasets to be saved. Also, you would still like to have the LIBNAME statement for SAVE libref, so that you can switch easily from WORK libref to specific path. so, the following two solutions you can use:
LIBNAME save %sysfunc(quote(%sysfunc(pathname(work)))) ;
the second way to do it is the following:
%let workLocation=%sysfunc(getoption(work));
libname SAVE "&workLocation.";

Read multiple external files into single dataset in SAS

Reading multiple external files in SAS.
For further manipulation, it looks good if these data could all be gathered in the same dataset, from which then usual data manipulation queries could be performed on.

Now, if you would like to conditionally to read multiple files that have different structure, you can conditionally change the input statement. Let's say that file has the different structure if its name ends with "d" character: