topic Re: Proc Export in Macro Loop in SAS Enterprise Guide

Proc Export in Macro Loop

sun538 — Tue, 10 Nov 2020 15:53:03 GMT

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

Re: Proc Export in Macro Loop

jarapoch — Tue, 10 Nov 2020 15:59:08 GMT

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

Re: Proc Export in Macro Loop

Reeza — Tue, 10 Nov 2020 16:04:36 GMT

Good catch!

Re: Proc Export in Macro Loop

sun538 — Tue, 10 Nov 2020 16:07:59 GMT

Thanks for your catch. Appreciate it!

Re: Proc Export in Macro Loop

jarapoch — Tue, 10 Nov 2020 16:08:02 GMT

Thank you, Reeza!!

Re: Proc Export in Macro Loop

PaigeMiller — Tue, 10 Nov 2020 16:08:31 GMT

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

Re: Proc Export in Macro Loop

Tom — Tue, 10 Nov 2020 16:31:27 GMT

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