Proc Export in Macro Loop

[sun538]

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

[jarapoch · Accepted solution]

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

[Reeza]

Good catch!

[jarapoch]

Thank you, Reeza!!

[sun538]

Thanks for your catch. Appreciate it!

[PaigeMiller]

In the future, for problems like this, show us the log, along with the original code.

[Tom]

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