Help in merging two datasets based condition

[batu544]

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

[Reeza]

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]

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 · Accepted solution]

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]

Thank you KurtBremser .. It worked .. 🙂