topic Re: Running Procs Within a Do Loop in SAS Programming

Running Procs Within a Do Loop

JeffU — Tue, 25 Oct 2011 20:42:54 GMT

Hey all, question:

I have a dataset of users and what I'm looking to do is look through all of them and create a series of reports by user.

table work.users

Username

----------------

Smith

Jones

Andrews

My report code is

/***************************Set Dynamic Path and Filename***********************************/

ODS tagsets.HTMLpanel nogtitle Path="\\myreports\[username]"

File = "[username].xls" style=minimal

;

/*************Create Dynamic Table for each User*************/

proc sql;

create table [username] as

select sales_id, sales_date, price from sales where name = '[username]';

quit;

proc print data=[username];

quit;

ods html close;

In my other programming experience I'd use a do while loop but I'm stuck as to how to do it in SAS. Any help would be greatly appreciated.

Running Procs Within a Do Loop

ballardw — Tue, 25 Oct 2011 21:07:14 GMT

If I understand what you want to do the easiest may be a BY group process.

Proc sort data=Sales out=SortedSales;by username;run;

proc print data=SortedSales;

by username;

var sales_id sales_date price;

run;

Re: Running Procs Within a Do Loop

Reeza — Tue, 25 Oct 2011 21:09:45 GMT

Do you need to do it for all users or only a select users?

Have you looked at BY processing? Or Macro processing?

Basically theres a lot of ways to do it, I'd need more info to see what the best way to do it would be, but guessing you're going to go down the macro route if you want a file for each user.

See here for a basic idea:

http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/default.htm

HTH

Re: Running Procs Within a Do Loop

art297 — Tue, 25 Oct 2011 22:51:16 GMT

Yet another way of doing it, but one which would accept the code basically how you have written it, is to submit the code within a datastep using call execute. You can find an example of doing that in the following thread:

http://communities.sas.com/message/101028#101028

Since SAS inherently loops through the records during a datastep, you don't need to get overly loopy.

Re: Running Procs Within a Do Loop

Ksharp — Wed, 26 Oct 2011 06:12:42 GMT

Just as Art said. You can firstly make a macro to include these SQL proc print, then use call execute to loop this macro.

Ksharp