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.
So
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.
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;
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
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.