05-03-2017 01:50 AM
I have a master table with columns EMP_ID , location and salary
I want to create separate table for each location with the columns EMP_ID , location and salary
I can use the Proc SQL ; create table where location is "location name " , but i have around 100 locations and for each location i have to crerate a separate tabe so its difficult to write the where clause 100 times in the proc sql
is there any other way to do this , kindly help
the master dataset is like this
i want the tables to be created like below
table new york
05-03-2017 02:40 AM
What for? If you need analytics done on groups, use by-processing or a where condition. Putting data (location names) in structure (dataset names) will only make your life harder.
See Maxim 19.
05-03-2017 06:25 AM
@KurtBremser i am not using any analytics for this , the situations is like , i have to share the individually created table to each locatin separately
in a bulk i can't send because i contains some confidential data
kindly help for a solution ..
05-03-2017 06:51 AM - edited 05-03-2017 06:53 AM
First of all, you need to be aware that using strings from data in file/dataset names might have unexpected side effects. Datasets in particular have a 32 character name limit, and no characters apart from a-z,0-9,_ are allowed. So you need to adapt your location variable first by using translation functions.
Then you can do
proc sort data=have (keep=location) out=lookup nodupkey ; by location; run; data _null_; call execute('data '); do until(eof1); set lookup end=eof1; call execute(trim(location) !! ' '); end; call execute('; set have; select (location);'); do until(eof2); set lookup end=eof2; call execute('when("' !! trim(location) !! '") output ' !! trim(location) !! ';'); end; call execute('end; run;') run;
This creates a data step dynamically from your lookup data and will need just two passes (one sort, one final) through the master dataset.