BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sotarkadin
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

View solution in original post

5 REPLIES 5
FreelanceReinh
Jade | Level 19

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

Sotarkadin
Calcite | Level 5

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.

FreelanceReinh
Jade | Level 19

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;

 

Sotarkadin
Calcite | Level 5

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?

FreelanceReinh
Jade | Level 19

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1074 views
  • 0 likes
  • 2 in conversation