topic SAS Proc Export statement - Export multiple subset files via loop in SAS Procedures

SAS Proc Export statement - Export multiple subset files via loop

Terho — Mon, 09 Apr 2018 22:55:34 GMT

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

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

ballardw — Mon, 09 Apr 2018 23:13:32 GMT

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.

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

Ksharp — Tue, 10 Apr 2018 13:13:04 GMT

%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

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

Terho — Tue, 10 Apr 2018 13:38:41 GMT

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!