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

Reply
Occasional Contributor
Posts: 8

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

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 IDLOCATIONSSALARY
1new york20000

table mumbai

EMP IDLOCATIONSSALARY
2mumbai3000

table bangalore

EMP IDLOCATIONSSALARY
3bangalore5000

table delhi

EMP IDLOCATIONSSALARY
4delhi100000
Super User
Posts: 6,982

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

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

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

Super User
Posts: 6,982

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 116 views
  • 1 like
  • 2 in conversation