DATA Step, Macro, Functions and more

Help in merging two datasets based condition

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Help in merging two datasets based condition

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.. Smiley Happy 

 

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 Smiley Happy 

 

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


Accepted Solutions
Solution
‎10-21-2017 01:43 AM
Super User
Posts: 7,868

Re: Help in merging two datasets based condition

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 19,878

Re: Help in merging two datasets based condition

[ Edited ]

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.. Smiley Happy 

 

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 Smiley Happy 

 

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


 

Occasional Contributor
Posts: 5

Re: Help in merging two datasets based condition

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

 

Solution
‎10-21-2017 01:43 AM
Super User
Posts: 7,868

Re: Help in merging two datasets based condition

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Help in merging two datasets based condition

Posted in reply to KurtBremser

Thank you KurtBremser .. It worked .. Smiley Happy 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 130 views
  • 2 likes
  • 3 in conversation