SAS Proc Export statement - Export multiple subset files via loop

[Terho]

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

[ballardw]

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.

[Ksharp · Accepted solution]

%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

[Terho]

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!