Naming exported files with variable in SAS

Question

I am looking to export data from SAS to .csv with PROC EXPORT. I would like the name of the file to change based on the value of a variable. Is that possible?

PROC EXPORT DATA= WORK.A 
OUTFILE= "c:\folders\filenameVAR1.csv" 
DBMS=CSV LABEL REPLACE;
PUTNAMES=YES;
RUN;

I would like to add the value of a variable (eg. VAR1) at the end of my file name as shown above. I would like a file named filenameVAR1.csv and then when I change the variable it would be called filenameVAR2.csv. Thanks

Sure you can use macro variables &VAR1.. But what type of variables are you working with? Scalars? Please extend code example to show definition of variables. — Parfait

Joe Joe · Accepted Answer · 2019-06-27T21:45:58

In order to do this, you'll need to pull that value out into a macro variable. You can't directly use a data step variable in this manner, because PROC EXPORT doesn't interact with the data step (even if it makes use of it in some cases).

One question would be, when you say ' the value of a variable ' what do you mean? If you mean "the value of one variable on one row", even if that's actually present on all rows, you can do it pretty easily:

proc sql;
  select max(var1) into :var1
    from work.a
  ;
quit;

Then you have a &VAR1. macro variable you can insert into your export:

PROC EXPORT DATA= WORK.A 
OUTFILE= "c:\folders\filename&VAR1..csv" 
DBMS=CSV LABEL REPLACE;
PUTNAMES=YES;
RUN;

Note the extra . that terminates the macro variable.

Now, if this value changes, and you want a new file for each set of rows with a common value for var1, then it's different. You can't do that directly in proc export, but since you're writing out a CSV you could do it yourself!

data _null_;
  set work.a;
  length file_w_Var1 $255;
  file_w_var1 = cats('c:\folders\filename',VAR1,'.csv');
  file a filevar=file_w_var1 dlm=',' dsd;
  put 
    var1 
    var2 $ 
    var3 
    var4 
    var5 $ 
    var6
   ;
run;

(obviously with real variable names and appropriate $ and whatnot).

You could do the export, copy the export code from the log into a program file, and just change the file statement as I do above (and add the creation of that variable code) in order to get it to work. For this to work how you want it must be sorted by var1 (at least grouped by it, if not sorted).

What the filevar option does is it tells SAS to look at a variable and use that for the file location, instead of whatever is on the file statement - so there is a dummy a there (unrelated to the name of your dataset). It will make a new file each time that value changes.

Naming exported files with variable in SAS

1 Answers