BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PeterClemmensen
Tourmaline | Level 20

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
;

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
Kurt_Bremser
Super User

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
Tourmaline | Level 20

Unfortunately I have to export as .xlsx format.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

Cool, thank you so much for your help 🙂

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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