topic how to create multiple table from the observations of a single table in SAS Programming

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

soham_sas — Wed, 03 May 2017 05:50:27 GMT

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

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

Kurt_Bremser — Wed, 03 May 2017 06:40:26 GMT

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.

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

soham_sas — Wed, 03 May 2017 10:25:50 GMT

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

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

Kurt_Bremser — Wed, 03 May 2017 10:53:43 GMT

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.