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 🙂
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.
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
@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?
Around a couple million observations.
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?
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
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.