DATA Step, Macro, Functions and more

Export multiple datasets to multiple excel files

Accepted Solution Solved
Reply
PROC Star
Posts: 766
Accepted Solution

Export multiple datasets to multiple excel files

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? Smiley Happy

 

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
;

 


Accepted Solutions
Solution
‎04-06-2017 08:18 AM
Super User
Super User
Posts: 7,993

Re: Export multiple datasets to multiple excel files

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;

 

View solution in original post


All Replies
Super User
Posts: 7,859

Re: Export multiple datasets to multiple excel files

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 766

Re: Export multiple datasets to multiple excel files

Posted in reply to KurtBremser

Unfortunately I have to export as .xlsx format.

Super User
Super User
Posts: 7,993

Re: Export multiple datasets to multiple excel files

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.

PROC Star
Posts: 766

Re: Export multiple datasets to multiple excel files

[ Edited ]

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)||? Smiley Happy

 

Solution
‎04-06-2017 08:18 AM
Super User
Super User
Posts: 7,993

Re: Export multiple datasets to multiple excel files

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;

 

PROC Star
Posts: 766

Re: Export multiple datasets to multiple excel files

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?

Super User
Super User
Posts: 7,993

Re: Export multiple datasets to multiple excel files

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;
PROC Star
Posts: 766

Re: Export multiple datasets to multiple excel files

Cool, thank you so much for your help Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 168 views
  • 2 likes
  • 3 in conversation