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
%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
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.
%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
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.