topic Re: SAS merge on different variables based on availability in SAS Programming

SAS merge on different variables based on availability

Shirin — Fri, 22 Sep 2017 15:39:07 GMT

Hello,

I'm tryingh to do a SAS merge, based on two different variables.

If variable A is not blank then merge on variable A, and if it's blank, then merge on variable B instead.

Is this possible, or do I have to break up the file based on availability of variable A and do two different merges?

Any healp would be greatly appreciated.

Thank you 🙂

Re: SAS merge on different variables based on availability

ballardw — Fri, 22 Sep 2017 15:45:47 GMT

I think that you need to provide examples of your two datasets (if you have LOTS of other variables then only a couple please if they aren't involved in the problem) and what you expect the result to look like.

The are a number of ways to combine datasets and a concrete example will provide much better responses instead of us guessing as to the actual desired output.

Re: SAS merge on different variables based on availability

Reeza — Fri, 22 Sep 2017 15:57:06 GMT

Do the join in SQL and you can have a condition on one side, or you can pre-create the variable using the COALESCE(C) function.

*assuming a character variable;

merge_var = coalesceC(a, b);

or via SQL

from table1 as t1
join table2 as t2
on coalesce(t1.A, t1.B) = t2.A

Re: SAS merge on different variables based on availability

Shirin — Fri, 22 Sep 2017 20:47:51 GMT

I have a very huge dataset, both vertically and horizontally... But I think here is a simple way to demonstrate what I have and what I want:

data test1;

input Name ID1 ID2 othervar1 $ ;

datalines;

Sharon 185 18 fge

James 257 25 sdfh

Mike 253 25 awer

Matt 814 81 sduifh

John 567 56 fhjuk

Sherri 453 45 uih

;

run;

data correspondence;

input ID1 ID2 othervar2 $;

datalines;

185 18 rtr

. 25 sd

814 81 we

567 56 hj

453 45 qw

;

run;

want:

Name ID1 ID2 othervar1 othervar2 ;

Sharon 185 18 fge rtr

James 257 25 sdfh sd

Mike 253 25 awer sd

Matt 814 81 sduifh we

John 567 56 fhjuk hj

Sherri 453 45 uih qw

Re: SAS merge on different variables based on availability

Reeza — Fri, 22 Sep 2017 20:59:25 GMT

@Shirin wrote:

I have a very huge dataset, both vertically and horizontally... But I think here is a simple way to demonstrate what I have and what I want:

What's your definition of 'huge'? 100,000 or 1 trillion?

Re: SAS merge on different variables based on availability

ballardw — Fri, 22 Sep 2017 21:53:14 GMT

For your example data a merge (or join) on ID2 works.

data test1;
input Name $ ID1 ID2 othervar1 $ ;
datalines;
Sharon 185 18 fge
James 257 25 sdfh
Mike 253 25 awer
Matt 814 81 sduifh
John 567 56 fhjuk
Sherri 453 45 uih
;
run;
 
data correspondence;
input ID1 ID2 othervar2 $;
datalines;
185 18 rtr
. 25 sd
814 81 we
567 56 hj
453 45 qw
;
run;

proc sort data=test1;
   by id2;
run;
proc sort data=correspondence;
   by id2;
run;

data want;
   merge correspondence test1  ;
   by id2;
run;

Note addition of $ so name gets read as character. Order of variables and records is different but the values are the same.

Or is you example data incomplete to exercise all the options required?

Re: SAS merge on different variables based on availability

Shirin — Wed, 27 Sep 2017 13:22:59 GMT

Around a couple million observations.

Re: SAS merge on different variables based on availability

Shirin — Wed, 27 Sep 2017 13:57:14 GMT

Hi Reeza, I didnt know thsi function existed, thanks!

So I'm saying join it on:

from t1: A if exists and B if A doesnt exist

from t2: on A

But how do I say to join it from t2, on the same variable as in t1?