topic Re: Export multiple datasets to multiple excel files in SAS Programming

Export multiple datasets to multiple excel files

PeterClemmensen — Thu, 06 Apr 2017 09:32:26 GMT

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
;

Re: Export multiple datasets to multiple excel files

Kurt_Bremser — Thu, 06 Apr 2017 09:39:05 GMT

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.

Re: Export multiple datasets to multiple excel files

PeterClemmensen — Thu, 06 Apr 2017 09:41:33 GMT

Unfortunately I have to export as .xlsx format.

Re: Export multiple datasets to multiple excel files

RW9 — Thu, 06 Apr 2017 09:47:02 GMT

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.

Re: Export multiple datasets to multiple excel files

PeterClemmensen — Thu, 06 Apr 2017 10:12:39 GMT

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)||? 🙂

Re: Export multiple datasets to multiple excel files

RW9 — Thu, 06 Apr 2017 10:21:17 GMT

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;

Re: Export multiple datasets to multiple excel files

PeterClemmensen — Thu, 06 Apr 2017 11:38:06 GMT

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?

Re: Export multiple datasets to multiple excel files

RW9 — Thu, 06 Apr 2017 12:14:53 GMT

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;

Re: Export multiple datasets to multiple excel files

PeterClemmensen — Thu, 06 Apr 2017 12:17:53 GMT

Cool, thank you so much for your help 🙂