BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sun538
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
jarapoch
Obsidian | Level 7
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

View solution in original post

6 REPLIES 6
jarapoch
Obsidian | Level 7
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
Super User
Good catch!
jarapoch
Obsidian | Level 7

Thank you, Reeza!!

sun538
Obsidian | Level 7
Thanks for your catch. Appreciate it!
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Tom
Super User Tom
Super User

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3813 views
  • 10 likes
  • 5 in conversation