BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
batu544
Fluorite | Level 6

Hi All, 

 

I am new to SAS and trying to write my new program in SAS and I am not able to proceed for a below situation. If anyone can guide on this issue, it will be a great help.. 🙂 

 

Here is my requirement. 

 

I have one SAS dataset (dept) having below data. ( below data is actually coming from a DB2 table using PROD SQL).

  

ID_CD       STATE   DEPARTMENT ..... 
100          CT        HR
200          ME        IT
300          VA        MR
400          NY        SL
500 CT HR

 

 I have another Db2 table(universal_table) having below data. 

UNIV_cd     UNIV_DESC
CT          Connecticut
ME          Massachusetts
VA          Virginia 
NY          NewYork
HR          Human Resource
IT          Information Technology
MR          Marketing Research
SL          Sales

I want a final output like below.. 

 

ID_CD       STATE STATE_DESC     DEPARTMENT  DEPART_DESC
100          CT    Connecticut      HR       Human Resource
200          ME    Massachusetts    IT       Information Technology
300          VA    Virginia         MR       Marketing Research
400          NY    NewYork          SL       Sales
500 CT Connecticut HR Human Resource

 

 Any guidance to get this required result will be appreciated 🙂 

 

Please note: This is just a sample data. There are lot more columns present in dept dataset and I need to get the description from universal table for all. 

 

 

Thank you

pk

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Create a format from your universal table and apply that. No sorting or joining needed:

data cntlin;
set universal_table (rename=(univ_cd=start univ_desc=label));
retain type 'C' fmtname 'unifmt';
run;

proc format library=work cntlin=cntlin;
run;

data final_output;
set dept;
state_desc = put(state,$unifmt.);
depart_desc = put(department,$unifmt.);
run;

View solution in original post

4 REPLIES 4
Reeza
Super User

You're looking at match merging. 

See examples and details here

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=n1tgk0uanvisvon1r26lc036k0w7.htm&docsetVer...

 

However, IMO, this is much easier in SQL. 

http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=p0xbwe8mkbjx9zn1fkxw7skq9efd.htm&docsetV...

 

or 

 

https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-using-proc-sql/

 

If you're still having issues please feel free to post your code and log, and detail any issues you're having.

 

EDIT: One key idea - you can list a table multiple times in a single SQL query so you can join on different fields at once in SQL. In a data step if you're merging on multiple values you need multiple data steps. 

 


@batu544 wrote:

Hi All, 

 

I am new to SAS and trying to write my new program in SAS and I am not able to proceed for a below situation. If anyone can guide on this issue, it will be a great help.. 🙂 

 

Here is my requirement. 

 

I have one SAS dataset (dept) having below data. ( below data is actually coming from a DB2 table using PROD SQL).

  

ID_CD       STATE   DEPARTMENT ..... 
100          CT        HR
200          ME        IT
300          VA        MR
400          NY        SL
500 CT HR

 

 I have another Db2 table(universal_table) having below data. 

UNIV_cd     UNIV_DESC
CT          Connecticut
ME          Massachusetts
VA          Virginia 
NY          NewYork
HR          Human Resource
IT          Information Technology
MR          Marketing Research
SL          Sales

I want a final output like below.. 

 

ID_CD       STATE STATE_DESC     DEPARTMENT  DEPART_DESC
100          CT    Connecticut      HR       Human Resource
200          ME    Massachusetts    IT       Information Technology
300          VA    Virginia         MR       Marketing Research
400          NY    NewYork          SL       Sales
500 CT Connecticut HR Human Resource

 

 Any guidance to get this required result will be appreciated 🙂 

 

Please note: This is just a sample data. There are lot more columns present in dept dataset and I need to get the description from universal table for all. 

 

 

Thank you

pk


 

batu544
Fluorite | Level 6

Thanks for reply. 

 

I was able to merge the table by using proc sql, but issue is I need to do the join for more than 100 times to get the description for all those fields. Is there any other way  like macros or something ? 

 

Thanks

pk

 

Kurt_Bremser
Super User

Create a format from your universal table and apply that. No sorting or joining needed:

data cntlin;
set universal_table (rename=(univ_cd=start univ_desc=label));
retain type 'C' fmtname 'unifmt';
run;

proc format library=work cntlin=cntlin;
run;

data final_output;
set dept;
state_desc = put(state,$unifmt.);
depart_desc = put(department,$unifmt.);
run;
batu544
Fluorite | Level 6

Thank you KurtBremser .. It worked .. 🙂 

 

 

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
  • 4 replies
  • 899 views
  • 2 likes
  • 3 in conversation