topic Re: one to many merge data issues in SAS Programming

one to many merge data issues

hsigicherla — Sat, 25 Nov 2017 06:49:52 GMT

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.

Re: one to many merge data issues

Shmuel — Fri, 24 Nov 2017 09:18:20 GMT

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.

Re: one to many merge data issues

Kurt_Bremser — Fri, 24 Nov 2017 11:37:40 GMT

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

Re: one to many merge data issues

hsigicherla — Sat, 25 Nov 2017 06:54:44 GMT

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,

Re: one to many merge data issues

hsigicherla — Sat, 25 Nov 2017 07:00:22 GMT

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,