topic Re: Data merging in SAS Programming

Data merging

GS2 — Mon, 30 Sep 2019 12:30:31 GMT

Hello,

Using SAS 9.4.

Is it possible to merge 2 data sets by a specific ID that is not the same? Meaning I have 2 data sets where I want to keep all information from both data sets and have the data in a wide format. The only problem is the data is a 1:2 merge with ID that do not match. Is there a good method to perform this? Thank you

ID ID2

1 10

1 20

2 3

2 8

3 90

3 15

So I need to have 2 rows with ID 1 and merge in ID 10 and 20 on the same row as ID 1 to create the data set? Any thoughts would be helpful.

Re: Data merging

PeterClemmensen — Mon, 30 Sep 2019 12:32:37 GMT

@GS2 can you post what the two data sets that should result in your posted data set looks like?

Re: Data merging

GS2 — Mon, 30 Sep 2019 12:46:46 GMT

I cannot. The data sets are primarily PHI with the exception of the ID and a key of which IDs should be mapped together. I know it is not much to go on but my question was as much theoretical of if it could be done. Thank you

Re: Data merging

utrocketeng — Mon, 30 Sep 2019 13:40:51 GMT

I would create a third table that would hold the associations between ID and ID2 using PROC SQL. This third table would be primary table that table 1 and table 2 would join on.

Re: Data merging

ballardw — Mon, 30 Sep 2019 14:32:24 GMT

And how do we know that 90 and 15 is supposed to match 3? or 3 and 8 match 2?

Computers run on rules. Without a rule you are going to have a very hard time telling the computer what to do.

If you duplicate records for the first set would you have the same of records in both data sets so a one-to-one would work? Is the second data set then in the correct order to match with data one? If so this may help:

data onetemp;
   set dataone;
   output;
   output;
run;

data want;
   merge onetemp datatwo;
run;

otherwise you are going to have to come up with some rule for connecting to the two sets of values.

And perhaps some attention should be paid to having proper identification variables at compilation in the future.

Re: Data merging

Tom — Mon, 30 Sep 2019 15:20:51 GMT

Are you just saying you want to read two observations from the TWO for every observation read from ONE?

data want ;
  set one;
  do rep=1 to 2;
    set two;
    output;
  end;
run;

Re: Data merging

ed_sas_member — Mon, 30 Sep 2019 15:30:40 GMT

Hello @GS2 ,

I would suggest the "One-to-one reading" method to combine your datasets.

This method will create observations that contain all of the variables from each contributing data set.

The combines observations will be based on their relative position in each source data set.

data new;

set source1;

set source2;

run;

warning : you need to be sure that you have the same amount of observation in source data sets, and that the position of an observation in one dataset is the same than the one in the second source data set that you want to combine.

Hope this help!