BookmarkSubscribeRSS Feed
soham_sas
Quartz | Level 8

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
3 REPLIES 3
Kurt_Bremser
Super User

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
Quartz | Level 8

@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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 407 views
  • 1 like
  • 2 in conversation