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
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.)
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.)
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.
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;
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.