Desktop productivity for business analysts and programmers

How to use proc append in an iterative way

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

How to use proc append in an iterative way

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.


Accepted Solutions
Solution
‎05-12-2014 03:21 PM
PROC Star
Posts: 1,146

Re: How to use proc append in an iterative way

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

View solution in original post


All Replies
PROC Star
Posts: 1,146

Re: How to use proc append in an iterative way

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

Frequent Contributor
Posts: 82

Re: How to use proc append in an iterative way

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.

Solution
‎05-12-2014 03:21 PM
PROC Star
Posts: 1,146

Re: How to use proc append in an iterative way

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 326 views
  • 0 likes
  • 2 in conversation