Hi,
I have a macro to create several tables. I use a Do loop to create these tables using proc sql. I need to add a proc export to the loop, such that every time it creates a table, it exports it in excel to a path:
proc sql ;
select distinct month,count(distinct month) into :names1-,:c
from X;
quit;
%macro t;
proc sql;
%do i =1 %to &c;
create table &&names&i as
select *
from X
where strip(month)="&&names&i";
quit;
proc export
data=&&names&i
dbms=xlsx
outfile="\\...\&&names&i..xlsx"
replace;
run;
%end;
%mend t;
options mprint;
%t
However, proc export does not work after proc sql in macro loop. Could you please help me how to use proc export here to export each table that this loop generates? Thanks
Hi, sun538.
Try to put proc sql; sentence inside do loop.
proc sql ;
select distinct month,count(distinct month) into :names1-,:c
from X;
quit;
%macro t;
%do i =1 %to &c;
proc sql;
create table &&names&i as
select *
from X
where strip(month)="&&names&i";
quit;
proc export
data=&&names&i
dbms=xlsx
outfile="\\...\&&names&i..xlsx"
replace;
run;
%end;
%mend t;
options mprint;
%t
Hi, sun538.
Try to put proc sql; sentence inside do loop.
proc sql ;
select distinct month,count(distinct month) into :names1-,:c
from X;
quit;
%macro t;
%do i =1 %to &c;
proc sql;
create table &&names&i as
select *
from X
where strip(month)="&&names&i";
quit;
proc export
data=&&names&i
dbms=xlsx
outfile="\\...\&&names&i..xlsx"
replace;
run;
%end;
%mend t;
options mprint;
%t
Thank you, Reeza!!
In the future, for problems like this, show us the log, along with the original code.
Do you really need the individual datasets? Or just the sheets in the XLSX file? If so there is no need to for the SQL step.
Plus then you can use the REAL values of MONTH when creating the macro variables so you don't need the STRIP() function in the WHERE clause.
%macro t;
proc sql ;
select distinct quote(trim(month)) into :names1- from X;
%let c=&sqlobs;
quit;
%do i =1 %to &c;
proc export data=X(where=(month=&&names&i))
dbms=xlsx replace
outfile="\\...\&&names&i..xlsx"
;
run;
%end;
%mend t;
options mprint;
%t
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.