Good morning Guys,
I have a issue with One to many Merge statement. Should we follow a specific sequence to expect the correct output.
Lets say the two datasets are as below and already in sorted by C1 variable and C1 is the only common variable between D1 & D2.
(NOTE : Changed the data in datasets for better understanding)
D1(C1, C2, C3 are D1 data variables)
C1 C2 C3
1 A B
D2(C1, C4, C5 are D2 data variables)
C1 C4 C5
1 C D
1 E F
when i do
Merge D1(IN=A) D2(IN=B);
BY C1;
IF A AND B;
OUTPUT IS LIKE :
C1 C2 C3 C4 C5
1 A B C D
1 A B E F
But when am swapping datasets like
Merge D2(IN=A) D1(IN=B);
BY C1;
IF A AND B;
OUTPUT IS LIKE :
C1 C2 C3 C4 C5
1 A C ------> C3 AND C5 variables values on first observation or missing(printed as blanks)
1 A B E F
Please note that the data am using is different what i've given above.
When there is a same variable in both datasets, out of the by group, the data in the second dataset
overides the data in fiorst one. Thus is the reason you got different results.
If you use update instead merge, the first dataset is the main one and the second is treated as trunsuctions.
In case of missing value variable in the transaction, it will not replace the data in the main file.
Hi Shmuel,
Thanks for your explanation, am sorry for the confusion. But I didn't have common variables apart from group by variables.
Thanks Again,
Your merge will only create one result observation, which is identical both ways (only the sequence of variables in a row changes):
data d1;
input c1 c2 $ c3 $;
cards;
1 A B
;
run;
data d2;
input c1 c2 $ c4 $;
cards;
1 A E
1 C F
;
run;
data d3;
merge
d1 (in=a)
d2 (in=b)
;
by c1 c2;
if a and b;
run;
proc print data=d3 noobs;
run;
data d4;
merge
d2 (in=b)
d1 (in=a)
;
by c1 c2;
if a and b;
run;
proc print data=d4 noobs;
run;
Results:
c1 c2 c3 c4 1 A B E c1 c2 c4 c3 1 A E B
Hi KurtBremser,
sorry, I was not clear in the post. and yes you are correct. I've updated the post and yes in this case I could two observations. In my job am reading the data from a flat file but to my surprise first observation of group data variables are missing.
Thanks,
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.