I currently have this SAS program:
%let i = 1;
%macro test;
%let x = %scan(&idlist,&i,'*');
%do %until (&x= );
%put "&x";
ods html file="/sasdata//temp&x .xls" headtext="<style>
td {mso-number-format:\@}</style>";
proc print data=libanme.tablename uniform label;
var col1 col2 col3 col4 col5;
where col1 ="&x";
run;
ods html close;
%put "&x";
%let i = &i +1;
%let x = %scan(&idlist,&i,'*');
%end;
%mend test;
%test;
This program creates a separate xls file in each iteration.
My objective is to create a single dataset and append result of each iteration to that dataset and create a single output xls file containing the entire dataset.
Here's one way;
First, I'd put "proc sql; drop table want; quit;" right after "%macro test;". It's not required as long as you remember to delete your append dataset after each test, but I ALWAYS forget!
Then, inside the loop, in place of your proc print and ODS statements, try:
proc append base=want data=tablename(where=(col1 ="&x"));
and then your proc print and ODS statements can go after your %end;
Tom
I'm not quite clear on what you're trying to do.
It looks to me like each xls file contains the result of your "where" clause. To contain the entire dataset, wouldn't you just remove the "where" clause?
Tom
Thanks. What I want is that each iteration would contain a result of the where clause and append this result to a master data set. And then output this dataset in form of xls.
So if the table contains 1 million records and each iteration contains 5 records as per the where clause and if there are 5 iterations, then i want a single dataset created with the 5 * 5 = 25 records. And then output this dataset in xls.
Here's one way;
First, I'd put "proc sql; drop table want; quit;" right after "%macro test;". It's not required as long as you remember to delete your append dataset after each test, but I ALWAYS forget!
Then, inside the loop, in place of your proc print and ODS statements, try:
proc append base=want data=tablename(where=(col1 ="&x"));
and then your proc print and ODS statements can go after your %end;
Tom
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.