DATA Step, Macro, Functions and more

SAS merge on different variables based on availability

Reply
Contributor
Posts: 26

SAS merge on different variables based on availability

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 Smiley Happy

Super User
Posts: 13,913

Re: SAS merge on different variables based on availability

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.

Contributor
Posts: 26

Re: SAS merge on different variables based on availability

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

Super User
Posts: 23,980

Re: SAS merge on different variables based on availability


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?

 

 

Contributor
Posts: 26

Re: SAS merge on different variables based on availability

Around a couple million observations.

Super User
Posts: 13,913

Re: SAS merge on different variables based on availability

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?

Super User
Posts: 23,980

Re: SAS merge on different variables based on availability

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

Contributor
Posts: 26

Re: SAS merge on different variables based on availability

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?

Ask a Question
Discussion stats
  • 7 replies
  • 191 views
  • 2 likes
  • 3 in conversation