topic merge problem in SAS Procedures

merge problem

cypher85 — Mon, 24 Feb 2014 17:58:19 GMT

Hi,

Could someone help me out by letting me know why after the merge only the first instance of hindex has a value. As it's a one to many merge shouldn't both rows have a value for hindex? Thanks for any help!

Data check;

length name $15;

Input name hindex n;

Cards;

griffith . 93

;

run;

Data name;

length name $15;

Input name hindex n;

Cards;

griffith 2 93

;

run;

data check2;

merge check name ;

by n;

run;

Re: merge problem

Astounding — Mon, 24 Feb 2014 18:48:21 GMT

HINDEX should not be part of CHECK. The missing values from CHECK are overwriting the valid values from NAME. You could correct this at the last minute if need be:

data check2;

merge check (drop=hindex) name;

by name;

run;

Re: merge problem

cypher85 — Wed, 26 Feb 2014 17:02:34 GMT

I understand that in this instance "hindex" does not need to be on "check". This is simplified version of the merge problem I am having. But, this example still illustrates the issue. Even though "hindex" is in "check" all of the values are blank. When I merge with "name" shouldn't all values of "hindex" for the merge variable "n" be replaced by the values in "name". Only the first occurrence is being replaced.

Re: merge problem

Astounding — Wed, 26 Feb 2014 17:13:19 GMT

The values you are getting are to be expected. The process is actually more complex than you are envisioning. When MERGE reads data for a NAME, it reads each observation once. It does not read an observation from NAME multiple times. The process is more like this.

For the current value of NAME, see if the data set CHECK contains another observation. If so, read it in. Then see if the data set NAME contains another observation. If so, read it in. So the value of NAME overwrites the value in CHECK for the first observation. But that value never gets read again. It merely gets replaced by the value from CHECK on the remaining observations. A good reference:

http://www.ats.ucla.edu/stat/sas/library/nesug99/ad155.pdf