topic Conditionally Merging 2 Datasets in SAS Programming

Conditionally Merging 2 Datasets

jdchang — Mon, 18 Mar 2019 15:21:59 GMT

Hi all,

I've got data that looks like this:

data one;
input id key value;
datalines;
1 534 2905
2 . 6304
3 872 1108
;
run;

data two;
input id key;
datalines;
. 534
2 298
3 872
;
run;

I would like for it to be merged by both id and key such that I get the following results:

data end;
input id key value;
datalines;
1 534 2905
2 298 6304
3 872 1108
;
run;

At the moment I've only be merging by id am not sure of if the merge function can be used conditionally on if id is missing, then merge on key. I'm also not extremely familiar with proc sql, but am willing to use whatever works for the code. Many thanks!

Re: Conditionally Merging 2 Datasets

UdayGuntupalli — Mon, 18 Mar 2019 15:42:48 GMT

@jdchang ,
You could join the data set by using the common variables like so:

data merged;
   set one two;
   by key;
run;

Once you have a merged data set you can filter the data like so:

data want;

     set merged;

where key ne . ;

Re: Conditionally Merging 2 Datasets

Tom — Mon, 18 Mar 2019 15:44:59 GMT

How do you know to treat the first observation of the second dataset as if the value of ID as 1?

Re: Conditionally Merging 2 Datasets

Tom — Mon, 18 Mar 2019 15:48:31 GMT

You might need to post a more complete example of the issues you have.

Your current example is just a merge by ID and collapse values of KEY and ignore values from second dataset that don't match.

data want;
  merge one (in=in1) two (rename=(key=key2));
  by id;
  if in1;
  key=coalesce(key,key2);
  drop key2;
run;

Re: Conditionally Merging 2 Datasets

Astounding — Mon, 18 Mar 2019 16:08:57 GMT

You probably need to provide more information. Are these data sets really representative of the problem? Or do they contain more variables than you are showing?

If a variable has a missing value in TWO, you might as well throw out the entire observation. Even if you could match on the other variable, an observation with a missing value cannot contribute any new information.

Could there be more than one observation for a KEY?

Could there be more than one observation for an ID?

Do the data sets always match up the way you pictured: first observation with first observation, second observation with second observation, etc.

There are ways to handle most of the questions, but more direction is needed. Otherwise, we are just guessing.