BookmarkSubscribeRSS Feed
Shirin
Obsidian | Level 7

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 🙂

7 REPLIES 7
ballardw
Super User

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.

Shirin
Obsidian | Level 7

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

Reeza
Super User

@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?

 

 

Shirin
Obsidian | Level 7

Around a couple million observations.

ballardw
Super User

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?

Reeza
Super User

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

Shirin
Obsidian | Level 7

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 961 views
  • 2 likes
  • 3 in conversation