topic Dynamically generate tables from values in a field in SAS Programming

Dynamically generate tables from values in a field

hporter — Tue, 28 Jul 2020 19:03:28 GMT

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,

Re: Dynamically generate tables from values in a field

Kurt_Bremser — Tue, 28 Jul 2020 21:48:20 GMT

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;

Re: Dynamically generate tables from values in a field

ballardw — Tue, 28 Jul 2020 19:52:20 GMT

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.

Re: Dynamically generate tables from values in a field

Kurt_Bremser — Tue, 28 Jul 2020 21:51:28 GMT

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.

Re: Dynamically generate tables from values in a field

ghosh — Wed, 29 Jul 2020 00:48:12 GMT

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;

Re: Dynamically generate tables from values in a field

hporter — Wed, 29 Jul 2020 16:50:15 GMT

My apologies, yeah I should have said the end result.

I need separate files to be exported for each individual user.

So I don't actually need work tables of the users, I'm realizing that now, I can get away with just having one parent table, but then in the export I need to export 'by user_name.'

Re: Dynamically generate tables from values in a field

hporter — Wed, 29 Jul 2020 17:00:15 GMT

So I'm really only concerned with having several files as my end result, all with the name of the users that appear in my parent table.

I had googled before how to do a 'proc export by (field_name)' or something to no avail, although it makes sense that it would exist.

The data is all proprietary, but the parent table would be as such:
Column
Row Acct Date Status User Name
123 7/01/2020 ACT JOHN
876 7/03/2020 ACT JOHN
456 7/15/2020 ACT JANE
987 6/26/2020 NA MIKE
113 1/07/2018 ACT JANE

I need to export individual files for each user in the user_name field, so that I'd have a file for John, with all the accounts/data associated with him, a file for Mike, and all the accounts associated with him, a file for Jane, with all the accounts associated with her, etc.

Re: Dynamically generate tables from values in a field

hporter — Wed, 29 Jul 2020 17:10:49 GMT

Initially I was hoping to have individual files, but this is actually fantastic and might work depending on whether the client accepts it, regardless I learned something.

Thanks a lot!

Re: Dynamically generate tables from values in a field

ghosh — Wed, 29 Jul 2020 17:19:35 GMT

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.

Re: Dynamically generate tables from values in a field

hporter — Wed, 29 Jul 2020 17:28:56 GMT

Xlsx works great, and yes I can do that.

Thanks for your time