Export multiple datasets to multiple excel files

[PeterClemmensen]

Hi all. I have a dataset 'have', which has an ID variable. Fo reach distinct value of ID, I want to create a want data set called want_ID and then I want to export this dataset to an excel file in a specific location. After creating the datasets I want to erase them again. How do I go about this the simplest way? 🙂

Thank you in advance

data have;
	input ID $ var1 var2;
datalines;
1 100 200
4 300 400
4 500 200
4 200 100
7 400 200
1 300 166
1 300 100
7 400 400
;

data want_1;
	input ID $ var1 var2;
datalines;
1 100 200
1 300 166
1 300 100
;

data want_4;
	input ID $ var1 var2;
datalines;
4 300 400
4 500 200
4 200 100
;

data want_7;
	input ID $ var1 var2;
datalines;
7 400 200
7 400 400
;

[Kurt_Bremser]

If you use csv (or another textual format), you can change the outfile dynamically in a datastep (filevar= option). Just sort have by id and set the filevar= variable accordingly.

[PeterClemmensen]

Unfortunately I have to export as .xlsx format.

[RW9]

Should be pretty simple, first list of unique IDs, then generate the exports (note I can't test at the moment, but should be near enough):

proc sort data=have out=loop (keep=id) nodupkey;
  by id;
run;

data _null_;
  set loop;
  call execute('proc export data=have (where=(id="'||strip(id)||' outfile="c:\abc.xlsx";  sheet="'||strip(id)||'"; run;');
run;

Note, I assumed id is character in the above.

[PeterClemmensen]

Very cool solution, did not think of data _NULL_ and doing this in a call execute myself. However, I have to export the three datasets to separate workbooks?

And btw, what is the logick behind the ||s in ||strip(id)||? 🙂

[RW9 · Accepted solution]

Minor change then:

proc sort data=have out=loop (keep=id) nodupkey;
  by id;
run;

data _null_;
  set loop;
  call execute('proc export data=have (where=(id="'||strip(id)||' outfile="c:\abc'||strip(id)||'.xlsx";sheet="'||strip(id)||'"; run;');
run;

This will create abc<id>.xlsx whre id changes each time.

The double bar || means concatenate - its an older way of doing it, most of the time you would use catx, cats, catt etc. functions like:

proc sort data=have out=loop (keep=id) nodupkey;
  by id;
run;

data _null_;
  set loop;
  call execute(cats('proc export data=have (where=(id="',id,' outfile="c:\abc',id,'.xlsx";sheet="',id,'"; run;'));
run;

[PeterClemmensen]

Cool, this is the solution I will go with. Though I think you may be missing a parenthesis somewhere, can I get you to test it out, I cant get it to export?

[RW9]

Yes, to clarify, the string returned by the cats() function needs to be valid code as that gets pushed out the compiler after the dataset stops executing, so you can see the generated code in the log with + symbol.  Makes debugging easier.  Anyways, this example works - using sashelp dataset:

proc sort data=sashelp.class out=loop (keep=sex) nodupkey;
  by sex;
run;

data _null_;
  set loop;
  call execute(cats('proc export data=sashelp.class (where=(sex="',sex,'")) outfile="c:\test_',sex,'.xlsx";sheet="',sex,'"; run;'));
run;

[PeterClemmensen]

Cool, thank you so much for your help 🙂