BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Paul_NYS
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

5 REPLIES 5
Michelle
Obsidian | Level 7

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
Obsidian | Level 7

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
Super User

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

Paul_NYS
Obsidian | Level 7

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

jwsquillace
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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