Merging Data Question - ID values with different prefixes

Accepted Solution Solved
Reply
Contributor AD
Contributor
Posts: 31
Accepted Solution

Merging Data Question - ID values with different prefixes

I've got two datasets, each with an ID variable.  However, the values of the ID variables from each dataset do not have the same prefix.

For example, let's say I have 2 datasets as specified below:

Data One;
Input ID X;
Cards;
T_112233 2
T_445566 6
T_778899 8
;
Run;

Data Two;
Input ID Y;
Cards;
R_112233 10
R_445566 25
R_778899 30
;
Run;

How do I merge these files (i.e., how do I tell SAS that "T_112233" is the same as "R_112233")?

Thanks,

Andy


Accepted Solutions
Solution
‎12-04-2012 12:39 PM
Super User
Posts: 19,770

Re: Merging Data Question - ID values with different prefixes

Couple of ways, but the simplest is to change one to the other.

This can be done prior to the merge and then merged using a datastep, or during the merge via sql.

proc sql;

create table want as

select a.id as id_one, b.id as id_two, a.x, b.y

from one a

join two b

on scan(a.id, 2, "_")=scan(b.id, 2, "_"); *there are multiple ways to write this line, you could also use substr to replace the R in one to T...;

quit;

View solution in original post


All Replies
Solution
‎12-04-2012 12:39 PM
Super User
Posts: 19,770

Re: Merging Data Question - ID values with different prefixes

Couple of ways, but the simplest is to change one to the other.

This can be done prior to the merge and then merged using a datastep, or during the merge via sql.

proc sql;

create table want as

select a.id as id_one, b.id as id_two, a.x, b.y

from one a

join two b

on scan(a.id, 2, "_")=scan(b.id, 2, "_"); *there are multiple ways to write this line, you could also use substr to replace the R in one to T...;

quit;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 175 views
  • 0 likes
  • 2 in conversation