How to dynamically import external file in SAS

0
votes

Let's say that you dont know in advance the path of the file you need to import, but you have the path defined in a table that you have created during run-time. Importing it using macros is not correct, as macros are being generated before run-time, and thereby, the macro values could not reach the data which will be created during run-time.

So, how to import an external file in SAS Base (SAS EG, SAS DI,...), for which the path you know only during the run-time?

1
sasenterprise-guide
I don't understand why you are saying macros are not a viable option. Actually, I'm not quite sure what you mean by runtime either. Can you elaborate on this? Also can you give more detail about the contents of the path? Will it only ever contain a single file? - Robert Penridge
@RobertPenridge : One of many drawbacks macro facility have is that macro statements are being processed and statically added to code before the actual program execution. This means, in case above, if you need to import an external file, which filepath you know only once the program is being executed (either through complex query to a database, or getting it through web service, etc.), macro processor is incapable of reaching such data, as macro processor is being run before such information could be generated. am I wrong? - Sale
Yes I think you may have some minsunderstanding about how the macro environment interacts with regular SAS code. I can't speak for DI studio as I haven't used it, but I know in base SAS you can definitely reference files via macros, when you don't know the files until run time. - Robert Penridge

1 Answers

1
votes

SAS Base

data test;
  Path='/path/to/your/csv/file.csv';
run;

data _null_;
  set test;
  rc=filename("fid",Path);
run;

data csvData(drop=path);
  infile fid;
  input ...
run;

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.; rc=filename("fid",Path); run;

  • 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".