Solved
Contributor
Posts: 21

Create new variable based on separate table.

I currently have two tables.  One has highlevel portoflio information, the 2nd has the specific details for each portfolio.

Table 1:

cm_num           ref_nbr     trans_id

12345              AB456           1

12345              AB456           2

23456              BC258          1

23456              BC258          2

34567              CD987          1

34567              CD987           2

Table 2:

cm_num          ref_nbr           action         action_dt

12345              AB456              OCCN        3/1/2016

12345              AB456              OCCD        4/6/2016

23456             BC258              PCCD        3/15/2016

23456             BC258              OCCN        3/12/2016

34567             CD987             OCCN        4/6/2016

34567             CD987               PCCD        3/15/2016

As you can see Table A can have multiple entires from the same portfolio with different trans_id I need to look up the ref_nbr from Table 1 in table 2 and if it finds an OCCN for that ref_nbr I need it to take the action_dt and create a new variable called open_dt for every trans_nbr within that ref_nbr.   The results should look like this:

cm_num           ref_nbr     trans_id    open_dt

12345              AB456           1             3/1/2016

12345              AB456           2             3/1/2016

23456              BC258          1              3/12/2016

23456              BC258          2              3/12/2016

34567              CD987          1              4/6/2016

34567              CD987           2              4/6/2016

I am currently using SAS EG 9.4

Accepted Solutions
Solution
‎04-07-2016 03:42 PM
Posts: 1,242

Re: Create new variable based on separate table.

[ Edited ]

Assuming that the combination of CM_NUM and REF_NBR can be used as a key, you could try:

``````data want;
merge table1(in=a)
table2(where=(action='OCCN') rename=(action_dt=open_dt));
by cm_num ref_nbr;
if a;
drop action;
run;``````

If this doesn't work because either of the tables is not sorted by CM_NUM REF_NBR, try this:

``````proc sql;
create table want as
select a.*, b.action_dt as open_dt
from table1 a left join table2(where=(action='OCCN')) b
on a.cm_num=b.cm_num & a.ref_nbr=b.ref_nbr
order by cm_num, ref_nbr, trans_id;
quit;``````

Both solutions would create missing values for OPEN_DT if no matching record with action='OCCN' was found in table 2. In case of multiple matching records with action='OCCN' in table 2, you will need to provide a rule for this situation.

(Edit: Just improved code indentation.)

All Replies
Solution
‎04-07-2016 03:42 PM
Posts: 1,242

Re: Create new variable based on separate table.

[ Edited ]

Assuming that the combination of CM_NUM and REF_NBR can be used as a key, you could try:

``````data want;
merge table1(in=a)
table2(where=(action='OCCN') rename=(action_dt=open_dt));
by cm_num ref_nbr;
if a;
drop action;
run;``````

If this doesn't work because either of the tables is not sorted by CM_NUM REF_NBR, try this:

``````proc sql;
create table want as
select a.*, b.action_dt as open_dt
from table1 a left join table2(where=(action='OCCN')) b
on a.cm_num=b.cm_num & a.ref_nbr=b.ref_nbr
order by cm_num, ref_nbr, trans_id;
quit;``````

Both solutions would create missing values for OPEN_DT if no matching record with action='OCCN' was found in table 2. In case of multiple matching records with action='OCCN' in table 2, you will need to provide a rule for this situation.

(Edit: Just improved code indentation.)

Contributor
Posts: 21

Re: Create new variable based on separate table.

Based on the way some of the extra columns in the table worked I had to use the proc sql; code as the merge code kept overwriting data in table A becuase both tables have some of the columns named the same but were not pertinant to this issue.

Thank you so much as at first glance(I have not checked row for row on my sample data to ensure the data integrity) it seems to have worked perfectly.

Posts: 1,242

Re: Create new variable based on separate table.

[ Edited ]

You're welcome.

To avoid the overwriting in the data step, you could add a KEEP= or DROP= dataset option to TABLE2.
Example:

``````data want;
merge table1(in=a)
table2(keep=cm_num ref_nbr action action_dt
where=(action='OCCN') rename=(action_dt=open_dt));
by cm_num ref_nbr;
if a;
drop action;
run;``````

Contributor
Posts: 21

Re: Create new variable based on separate table.

Ok that makes sense, i dropped the columns I didn't need and that were named the same just with different data and that fixed it thank you.  One question I have is if both tables have a column named for example; card_num.  When it merges them which table is it coming from if for example it had different data, would it be the 2nd table?

Posts: 1,242

Re: Create new variable based on separate table.

[ Edited ]

Yes, the value from TABLE2 would overwrite that from TABLE1. If you set the following SAS system option, SAS will inform you in the log about which variables are overwritten and in which "direction":

``options msglevel=I;``

Here's an example of the type of log messages you will get with this option setting:

`INFO: The variable cm_num on data set WORK.TABLE1 will be overwritten by data set WORK.TABLE2.`

Edit: The above message occurred when I used ref_nbr as the only BY variable. BY variables will not occur in these messages, because they have to be in both datasets and by nature of match merging it's impossible that one of them is overwritten by a different value.

🔒 This topic is solved and locked.