Merging two data sets

[Paul_NYS]

Hi

I am trying to merge two data sets that have one common variable. There are some records that are shared between them. What I want to do is to create a merged data set that has all the records in the bigger 's1net' data set with the columns from the smaller 'freedkids' data set merged in for the IDs that are in the 's1net' data set and in the 'freedkids' data set. When I run the below merge, I am getting the correct records and all the columns are merged together, however, there are no values for the 'freedkids' data set variables, just the variable headers.

What am I doing wrong?

Paul

data allKids1;

merge freedkids s1net (in=a);

by entity_id;

if a;

run;

[Michelle]

Are both sets sorted by entity_id? If so, is entity_id exactly same variable type/length in both sets (if characters involved, are they all the same case)?

[Paul_NYS]

Yes, they are both sorted by entity_id prior. And both instances of entity_id are the same type/lengths. And the values of entity_id are all numeric.

Paul

[ballardw · Accepted solution]

Try reversing the order of the datasets on the merge statement.

[Paul_NYS]

That did it Ballardw. I did not think that mattered. Thanks a lot!

[jwsquillace]

Make sure that the variables from the freekids data set do not also exist on the s1net data set.

When we get this question in Tech Support, 90% of the time the answer is extraneous variables on the second data set.

If variables on s1net that match variables on freedkids data set and have values, you may need to do a merge and test for missing value.  Here is one possibility:

data test1;
infile datalines truncover;
input id var1 var2;
datalines;
1 1 1
2 2
3 3 3
4 4
;

data test2;
infile datalines truncover;
input id var2 var3;
datalines;
1 11 11
2 21 21
3 . 31
4 41 41
5 51 51
;

data result;
  merge test1 (in=in1) test2(in=in2 rename=(var2=var22));
if in1 then do;
  if  in2 then do;
    if var2 = . then var2 = var22;
  end;
end;
else if in2 then var2 = var22;
run;

proc print data=result; run;

Cheers,

Jan