topic Re: How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders in SAS Data Management

How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders

Beartato — Wed, 25 May 2016 01:26:34 GMT

Good evening, everyone! I hope you can forgive me for the somewhat newbie question, but my Googling abilities have failed to confirm the exact finding for which I'm searching.

I have two datasets which need to be merged specifically by ResponseID. However, they have different variables (the second dataset was recently generated with a new and different set of variables). I had originally been hoping to simply paste the new variables to the older dataset, but as it happens, the ResponseIDs came through in a different order than in the original dataset. Is there a dataset merging function in SAS which would merge the datasets *specifically on ResponseID*. I am thinking that SAS has a merging function which would add the new variables to the existing dataset, and match all values for those variables to the order as they appear in the old dataset. As a note, the datasets have the same ResponseIDs (e.g., 1,2,3,4,5 both exist within the two datasets), but in different orders (i.e., the second dataset was produced in order 4,2,1,5,3). See examples of old dataset, new dataset, and desired dataset.

Old dataset example

ResponseID	Variable 1	Variable 2	Variable 3	Variable 4	Variable 5
1	50	50	50	45	0
2	100	90	80	70	60
3	20	0	0	0	0
4	0	0	0	0	0
5	100	100	100	100	100

New dataset example:

ResponseID	Variable 6	Variable 7
4	10	10
2	50	25
1	60	30
5	100	100
3	0	0

Desired dataset example (but in the actual, non example dataset, this includes 7000 datapoints)

ResponseID	Variable 1	Variable 2	Variable 3	Variable 4	Variable 5	Variable 6	Variable 7
1	50	50	50	45	0	60	30
2	100	90	80	70	60	50	25
3	20	0	0	0	0	0	0
4	0	0	0	0	0	10	10
5	100	100	100	100	100	100	100

Given the tremendous size of the dataset (as well as a slight time crunch), it is not feasible to execute this task manually (as in, finding each unique ResponseID and matching them to the original dataset's order, then importing the data for the new variables in that matched order).

Thanks so much for your time!

Re: How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders

JerryLeBreton — Tue, 14 Aug 2018 14:07:34 GMT

This is all you need.

proc sort data=new;

by responseid;

run;

data want;

merge old new;

by responseid;

run;

But its important to know how it works. The doco's pretty good.

Editor's Note: The above code will work by sorting the data; JBailey's code works without requiring a sort first.

Re: How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders

Reeza — Wed, 25 May 2016 02:39:00 GMT

Just a quick note on how SAS handles variables that have the same name, the get overwritten by the last dataset in the MERGE statement.

If you want to keep all variables, rename your variables in each dataset so they're unique.

Otherwise, as indicated, the BY statement will match the observations to merge by responseID

Re: How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders

Beartato — Wed, 25 May 2016 12:03:43 GMT

Thanks to you both for your prompt and comprehensive replies.

Reeza, you make a good point about ensuring that all the variable labels are unique. If I execute the code that JerryLeBreton supplied on, for example, the exemplary data set I put in the original post, will it preserve all Variables 1, 2, 3, 4, 5, 6, 7?

I'm hoping it can just add the variables to the end of the dataset, such that Variables 6 and 7 appear after 5.

Re: How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders

Reeza — Wed, 25 May 2016 12:49:27 GMT

Yes it would

Re: How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders

JBailey — Thu, 02 Aug 2018 19:00:11 GMT

Hi @Beartato

Just for fun...

data old;
   input ResponseID Variable1 Variable2 Variable3 Variable4 Variable5;
cards;
1	50	50	50	45	0
2	100	90	80	70	60
3	20	0	0	0	0
4	0	0	0	0	0
5	100	100	100	100	100
run;

data new;
   input ResponseID Variable6 Variable7;
cards;
4	10	10
2	50	25
1	60	30
5	100	100
3	0	0
run;

proc sql;
   create table merged_data as 
      select o.ResponseID, o.Variable1, o.Variable2, o.Variable3, o.Variable4, o.Variable5
           , n.Variable6, n.Variable7
    from old o,
         new n
   where o.ResponseID = n.ResponseID
   order by o.ResponseID;
quit;

Re: How to Merge Two Datasets with Different Variables, but ResponseIDs in Different Orders

fritzgerald — Thu, 04 Mar 2021 14:46:03 GMT

What if none of the variable are the same... meaning if we didn't have responsIDs in both table and only one table.