topic Re: Create new variable based on separate table. in SAS Programming

Create new variable based on separate table.

Sotarkadin — Thu, 07 Apr 2016 18:50:03 GMT

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

Re: Create new variable based on separate table.

FreelanceReinh — Thu, 07 Apr 2016 19:51:48 GMT

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.)

Re: Create new variable based on separate table.

Sotarkadin — Thu, 07 Apr 2016 19:42:15 GMT

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.

Re: Create new variable based on separate table.

FreelanceReinh — Thu, 07 Apr 2016 19:50:46 GMT

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;

Re: Create new variable based on separate table.

Sotarkadin — Thu, 07 Apr 2016 19:57:38 GMT

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?

Re: Create new variable based on separate table.

FreelanceReinh — Thu, 07 Apr 2016 20:18:33 GMT

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.