DATA Step, Macro, Functions and more

Merging two data sets

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Merging two data sets

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;


Accepted Solutions
Solution
‎05-02-2013 10:21 AM
Super User
Posts: 11,343

Re: Merging two data sets

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

View solution in original post


All Replies
Contributor
Posts: 39

Re: Merging two data sets

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)?

Regular Contributor
Posts: 216

Re: Merging two data sets

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

Solution
‎05-02-2013 10:21 AM
Super User
Posts: 11,343

Re: Merging two data sets

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

Regular Contributor
Posts: 216

Re: Merging two data sets

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

SAS Employee
Posts: 26

Re: Merging two data sets

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 354 views
  • 0 likes
  • 4 in conversation