topic Re: How to use proc append in an iterative way in SAS Enterprise Guide

How to use proc append in an iterative way

eagles_dare13 — Mon, 12 May 2014 16:55:40 GMT

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.

Re: How to use proc append in an iterative way

TomKari — Mon, 12 May 2014 17:17:23 GMT

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

Re: How to use proc append in an iterative way

eagles_dare13 — Mon, 12 May 2014 17:21:55 GMT

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.

Re: How to use proc append in an iterative way

TomKari — Mon, 12 May 2014 19:21:10 GMT

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