Hello,
Currently I have a master table that I make several other tables from based on user_name.
Ex:
Master table
Table A columns: Acct, Date, Status, User_Name
then I inefficiently do something like:
proc sql;
create table John
as select * from work.A where name = 'John'
;quit;
proc sql;
create table Jane
as select * from work.A where name = 'Jane'
;quit;
Then later I export all of these, sticking to the same convention of writing several export statements all from my user_name work tables. It sucks.
If I only had a few users, and they never changed, I wouldn't care. But I have around 10 that vary based on hiring and firing, and i have this process automated, except I have to manually go in and edit users when and if they change.
I'm fairly new to programming, but have done similar things with macros/python/vba before. I just want to stick in SAS for this particular process.
I'm just running into a wall in my head because the user_names aren't knowable like months or days or something, so it would need to be a select distinct user_name, but I'm really at a loss.
Any help is appreciated,
Thank you,
Your goal seems to be wanting to export a discrete file per employee. Using the code below modified to your data you can create an Excel file with a separate worksheet for each person
FILENAME out '~/img/cars.xlsx';
proc sort data=sashelp.cars out=cars;
by origin;
run;
options nobyline;
ods listing close;
ods excel file=out options(sheet_name='#byval1');
proc print data=cars;
by origin;
run;
ods excel close;
ods listing;
If your names can all be used as valid SAS names, do this:
proc sql;
create table names as
select distinct name
from a
;
quit;
data _null_;
call execute('data');
do until (eof1);
set names end=eof1;
call execute(" " !! name);
end;
call execute('; select(name);');
do until (eof2);
set names end=eof2;
call execute('when ("' !! strip(name) !! '") output ' !! strip(name) !! ';');
end;
call execute('end; run;');
stop;
run;
When you have something like this :
proc sql; create table John as select * from work.A where name = 'John' ;quit; proc sql; create table Jane as select * from work.A where name = 'Jane' ;quit;
Coupled with "export all of these, sticking to the same convention of writing several export statements all from my user_name work table" very likely means that a report from Work.a could be made using some sort of BY group processing based on the values of Name.
You do not show any of the relationship between the contents of Work.A and the only variable, Name, does not appear in your Master Table description, so it is very hard to tell what you are attempting to do.
How do you as the use know that a "specific name" is needed?
Or are you actually meaning "I need to do this for the names that do appear in the data but I don't know which ones will be there."? <= That is exactly what BY group processing does.
Hint: Provide 1) a small example of your "Master Table", 2) Some example data of your Work.A and 3) what the final report would look like. If any summaries or such need to be done as part of the report provide the rules.
Oh, wait, you do these for export, not for sending/distributing the datasets themselves? You can do that much easier with BY group processing directly from the dataset.
Your goal seems to be wanting to export a discrete file per employee. Using the code below modified to your data you can create an Excel file with a separate worksheet for each person
FILENAME out '~/img/cars.xlsx';
proc sort data=sashelp.cars out=cars;
by origin;
run;
options nobyline;
ods listing close;
ods excel file=out options(sheet_name='#byval1');
proc print data=cars;
by origin;
run;
ods excel close;
ods listing;
That's great, if you like the solution could you mark it as accepted please.
Since you were not specific in the export file format, I used Excel since from my experience that is the most common format, keeping all the data in one file.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.