Help using Base SAS procedures

SAS Proc Export statement - Export multiple subset files via loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

SAS Proc Export statement - Export multiple subset files via loop

Hello,

I am trying to export multiple excel files based on a user inputted macro value Job.

 

Job will look like this (user input)

129692,135534, 135512

 

So, if the user inputs three jobs, three files will be created:

OUTPUT_129692

OUTPUT_135534

OUTPUT_135512

 

However, when I run the code below -- I cannot seem to get the export statement to subset the data successfully and output each individual file

The error returns %SCAN has too many arguments:

 

%macro loop;
%local i nextjob;
%do i=1 %to %sysfunc(countw("&job.",","));
%let nextjob = %scan(&job., &i);

 proc export data = temp.IDs_SUM (where=(jobid = "&nextjob."))
		outfile="/USERFOLDER/OUTPUT_&nextjob..xlsx"
		DBMS=XLSX REPLACE;
	run;

%end;
%mend loop;
%loop;

Any tips on what I am doing wrong?


Thanks


Accepted Solutions
Solution
‎04-10-2018 09:38 AM
Super User
Posts: 10,766

Re: SAS Proc Export statement - Export multiple subset files via loop

[ Edited ]
%let job = 129692,135534,135512 ;  %put &job;

%macro dummy;
   %do i=1 %to %sysfunc(countw(%bquote(&job),%str(,)));
      %let nextjob = %scan(%bquote(&job),&i);
      %put Nextjob is &nextjob ;
   %end;
%mend;

%dummy

View solution in original post


All Replies
Super User
Posts: 13,508

Re: SAS Proc Export statement - Export multiple subset files via loop

Basic issue is including commas in a list that will be used as a macro parameter.

 

Try making the list look like this:

129692 135534 135512

such as

%let job = 129692 135534 135512;

 

also the quotes in the countw likely aren't needed

%let job = 129692 135534 135512;

%macro dummy;
   %do i=1 %to %sysfunc(countw(&job," "));
      %let nextjob = %scan(&job.,&i);
      %put Nextjob is &nextjob ;
   %end;
%mend;

%dummy;

Note that with the commas you were generating

 

%scan(129692, 135534, 135512, &I) which is pretty obviously not quite correct.

Solution
‎04-10-2018 09:38 AM
Super User
Posts: 10,766

Re: SAS Proc Export statement - Export multiple subset files via loop

[ Edited ]
%let job = 129692,135534,135512 ;  %put &job;

%macro dummy;
   %do i=1 %to %sysfunc(countw(%bquote(&job),%str(,)));
      %let nextjob = %scan(%bquote(&job),&i);
      %put Nextjob is &nextjob ;
   %end;
%mend;

%dummy
Occasional Contributor
Posts: 19

Re: SAS Proc Export statement - Export multiple subset files via loop

Thank you Ksharp! This worked beautifully!

I guess the key point here is the use of the %bquote and %str function within the macro.


Thanks again!

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 241 views
  • 1 like
  • 3 in conversation