How to use proc append in an iterative way

[eagles_dare13]

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.

[TomKari]

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

[eagles_dare13]

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.

[TomKari · Accepted solution]

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