Hi there,
could you suggest for me how I could merge the two data sets by id, if I have two variables have equal values but in different rows.
DATA ONE;
INPUT ID VAR1 $;
CARDS;
1 C
1 A
1 D
2 A
2 B
3 C
;
RUN;
DATA TWO;
INPUT ID VAR2 $;
CARDS;
1 E
1 C
1 D
1 A
2 A
2 B
2 D
3 A
3 C
4 F
;
RUN;
/*The correct results should be as belwo
ID VAR1 VAR2
1 C C
1 A A
1 D D
2 A A
2 B B
3 C C
/*I tried the folloiwng code, but it didn't gave me the correct answer*/
data want;
merge one(in=a) two(in=b);
by ID;
if a;
if var1=var2;
run;
proc sql;
create table want as
select ONE.ID, VAR1, VAR2
from ONE left join TWO
on ONE.ID=TWO.ID and ONE.VAR1=TWO.VAR2;
quit;
Thanks draycut,
how I could please keep the other variables in both data sets when I'm using proc sql
for example if I have age in the first data set and weight in the second data set.
What other variables? Please be more specific.
DATA ONE;
INPUT ID VAR1 $ height;
CARDS;
1 C 167
1 A 168
1 D 168
2 A 172
2 B 174
3 C 171
;
RUN;
DATA TWO;
INPUT ID VAR2 $ age;
CARDS;
1 E 21
1 C 22
1 D 24
1 A 25
2 A 20
2 B 22
2 D 28
3 A 24
3 C 26
4 F 27
;
RUN;
/*The correct results should be as belwo
ID VAR1 VAR2 height age
1 C C 167 22
1 A A 168 24
1 D D 168 24
2 A A 172 20
2 B B 174 22
3 C C 171 26
@oalbalawi wrote:
DATA ONE; INPUT ID VAR1 $ height; CARDS; 1 C 167 1 A 165 1 D 168 2 A 172 2 B 174 3 C 171 ; RUN; DATA TWO; INPUT ID VAR2 $ age; CARDS; 1 E 21 1 C 22 1 D 24 1 A 25 2 A 20 2 B 22 2 D 28 3 A 24 3 C 26 4 F 27 ; RUN;
/*The correct results should be as belwo
ID VAR1 VAR2 hight age
1 C C 167 22
1 A A 168 24
1 D D 169 24
2 A A 172 20
2 B B 174 22
3 C C 171 26
Your result does not match your data. There's no height of 169 anywhere, and id = 1 var1 = A has an age of 25.
Apart from that, my code works:
data one;
input id var1 $ age;
cards;
1 C 167
1 A 165
1 D 168
2 A 172
2 B 174
3 C 171
;
run;
data two;
input id var2 $ height;
cards;
1 E 21
1 C 22
1 D 24
1 A 25
2 A 20
2 B 22
2 D 28
3 A 24
3 C 26
4 F 27
;
run;
proc sort data=one;
by id var1;
run;
proc sort data=two;
by id var2;
run;
data want;
merge
one (in=a)
two (in=b rename=(var2=var1))
;
by id var1;
if a and b;
run;
proc print data=want noobs;
run;
Result:
id var1 age height 1 A 165 25 1 C 167 22 1 D 168 24 2 A 172 20 2 B 174 22 3 C 171 26
Only the order changes, because of the sort. If you want to preserve the orignal order, you need to create a helper variable on which to re-sort after the merge.
Thanks KurtBremser.
Sorry, it was error typing in record the height of id=1 and the age id=1 & var1=A
Could you please show me any example of how I can keep the order of height and age because when I sort the data the age of each id will not be in the correct order.
Add a variable in table one:
data one;
input id var1 $ age;
keepsort = _n_;
cards;
1 C 167
1 A 165
1 D 168
2 A 172
2 B 174
3 C 171
;
run;
At the end, sort by that, and drop if you don't need it any longer:
proc sort data=want;
by keepsort;
run;
proc print data=want (drop=keepsort) noobs;
run;
Adapted result:
id var1 age height 1 C 167 22 1 A 165 25 1 D 168 24 2 A 172 20 2 B 174 22 3 C 171 26
Thanks KurtBremser.
Merge on both variables:
data one;
input id var1 $;
cards;
1 C
1 A
1 D
2 A
2 B
3 C
;
run;
data two;
input id var2 $;
cards;
1 E
1 C
1 D
1 A
2 A
2 B
2 D
3 A
3 C
4 F
;
run;
proc sort data=one;
by id var1;
run;
proc sort data=two;
by id var2;
run;
data want;
merge
one (in=a)
two (in=b rename=(var2=var1))
;
by id var1;
if a and b;
run;
proc print data=want noobs;
run;
Result:
id var1 1 A 1 C 1 D 2 A 2 B 3 C
Since var2 would be equal to var1 anyways, there's no need to keep it.
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.