BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hporter
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
ghosh
Barite | Level 11

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;

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

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;

 

ballardw
Super User

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.

hporter
Obsidian | Level 7
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.
Kurt_Bremser
Super User

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.

hporter
Obsidian | Level 7
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.'

ghosh
Barite | Level 11

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;
hporter
Obsidian | Level 7
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!
ghosh
Barite | Level 11

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.

hporter
Obsidian | Level 7
Xlsx works great, and yes I can do that.

Thanks for your time

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1032 views
  • 5 likes
  • 4 in conversation