Help using Base SAS procedures

merge problem

Reply
Contributor
Posts: 23

merge problem

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

                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;

Super User
Posts: 5,081

Re: merge problem

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;

Contributor
Posts: 23

Re: merge problem

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.

Super User
Posts: 5,081

Re: merge problem

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

Ask a Question
Discussion stats
  • 3 replies
  • 187 views
  • 0 likes
  • 2 in conversation