Hi
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
EMP ID | LOCATIONS | SALARY |
1 | new york | 20000 |
2 | mumbai | 3000 |
3 | bangalore | 5000 |
4 | delhi | 100000 |
i want the tables to be created like below
table new york
EMP ID | LOCATIONS | SALARY |
1 | new york | 20000 |
table mumbai
EMP ID | LOCATIONS | SALARY |
2 | mumbai | 3000 |
table bangalore
EMP ID | LOCATIONS | SALARY |
3 | bangalore | 5000 |
table delhi
EMP ID | LOCATIONS | SALARY |
4 | delhi | 100000 |
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.
@Kurt_Bremser 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 ..
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.