how to create multiple table from the observations of a single table

[soham_sas]

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

[Kurt_Bremser]

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.

[soham_sas]

@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 ..

[Kurt_Bremser]

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.