BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KevinL
Fluorite | Level 6

Good day all,

 

I'm still new at using SAS and have looked through some questions on this site and I'm still struggling.  I have two databases with numerous variables with the same name in each database:

 

DatabaseA

 

ID     Value     Type    Category

124   45          A         New

125   100        B         New

126   48          A         Old

 

DatabaseB

 

ID     Value     Type     Category

124   69          A          New

125   101        B          New

127   87          B          Old

 

The result I'm looking for is:

 

DatabaseC

 

ID     Value1   Value2

124   45          69

125   100        101

126   48          0

127   0            87

 

Any assistance will be greatly appreciated!

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
data a;
input ID     Value     Type $   Category $;
cards;
124   45          A         New
125   100        B         New
126   48          A         Old
; 

data b;
input ID     Value     Type $    Category $;
cards;
124   69          A          New
125   101        B          New
127   87          B          Old
;

proc sql;
create table want as
select COALESCE(a.id,b.id) as id ,coalesce(a.value,0) as value1, coalesce(b.value,0) as value2
from a full join b
on a.id=b.id ;
quit;

View solution in original post

13 REPLIES 13
novinosrin
Tourmaline | Level 20
data a;
input ID     Value     Type $   Category $;
cards;
124   45          A         New
125   100        B         New
126   48          A         Old
; 

data b;
input ID     Value     Type $    Category $;
cards;
124   69          A          New
125   101        B          New
127   87          B          Old
;

proc sql;
create table want as
select COALESCE(a.id,b.id) as id ,coalesce(a.value,0) as value1, coalesce(b.value,0) as value2
from a full join b
on a.id=b.id ;
quit;
KevinL
Fluorite | Level 6

Thanks for the quick reply!

 

I've applied the code to two databases I've imported from Excel:  Sep2011 and Oct2011.  In each database the identifier is called "ContractNo" and the values are called "BalanceOutstanding".  So I amended the code as follows:

 

proc sql;

create table want as

select COALESCE(Sep2011.ContractNo,Oct2011.ContractNo) as ContractNo ,coalesce(Sep2011.BalanceOutstanding,0) as value1, coalesce(Oct2011.BalanceOutstanding,0) as value2

from Sep2011 full join Oct2011

on Sep2011.ContractNo=Oct2011.ContractNo;

quit;

 

Unfortunately I'm getting this:

 

259 proc sql;

260 create table Want2 as

261 select COALESCE(Sep2011.ContractNo,Oct2011.ContractNo) as ContractNo

261! ,coalesce(Sep2011.BalanceOutstanding,0) as value1, coalesce(Oct2011.BalanceOutstanding,0) as

261! value2

262 from Sep2011 full join Oct2011

263 on Sep2011.ContractNo=Oct2011.ContractNo;

ERROR: Expression using equals (=) has components that are of different data types.

ERROR: The COALESCE function requires its arguments to be of the same data type.

264 quit;

 

I'm not sure if it's because some of the BalanceOutstanding in the databases are 0 or negative.  Or maybe I'm missing something obvious?

novinosrin
Tourmaline | Level 20

is your Sep2011.ContractNo,Oct2011.ContractNo   character or numeric. If char then replace 

COALESCE(Sep2011.ContractNo,Oct2011.ContractNo)

 

with

COALESCEC(Sep2011.ContractNo,Oct2011.ContractNo)

KevinL
Fluorite | Level 6

Thank you very much!

 

I checked the input data and the identifiers were formatted as test in the one database and as numbers in the other.  After formatting the code worked!

 

Last thing, if I want to exclude one of the Types while merging, would I use the Keep or Drop function and if so, how would it be added to the code you provided?

novinosrin
Tourmaline | Level 20

create table want(keep= your desired vars separated by space) as

KevinL
Fluorite | Level 6

Last question then I'm done for the day, I promise!

 

If I wanted to add a third database (Nov2011), how would the code change?

 

Thanks again, you've been most helpful!

novinosrin
Tourmaline | Level 20

Any number of questions are welcome. That's what the forum is for. 

 

Ok, do you mean to join another table?

if so, you can extend the syntax like

 

proc sql;
create table want as
select COALESCE(a.id,b.id) as id ,coalesce(a.value,0) as value1, coalesce(b.value,0) as value2
from a full join b
on a.id=b.id 
full join c
on b.id=c.id; quit;

  Just look for full join syntax examples online 

KevinL
Fluorite | Level 6

I want to merge three databases: Sep2011, Oct2011, Nov2011.  I would expect it to be something like this but it's not working.

 

proc sql;

create table want as

select COALESCE(Sep2011.ContractNo,Oct2011.ContractNo,Nov2011.ContractNo) as ContractNo, coalesce(Sep2011.BalanceOutstanding,0) as value1, coalesce(Oct2011.BalanceOutstanding,0) as value2, coalesce(Nov2011.BalanceOutstanding,0) as value3

from Sep2011 full join Oct2011

on Sep2011.ContractNo=Oct2011.ContractNo full join Nov2011 on Oct2011.ContractNo=Nov2011.ContractNo;

quit;

 

novinosrin
Tourmaline | Level 20

Are you getting an error or incorrect results? any log message plz

KevinL
Fluorite | Level 6

I've checked the formatting in the databases and run the code:

 

proc sql;

create table want as

select COALESCE(Sep2011.ContractNo,Oct2011.ContractNo,Nov2011.ContractNo) as ContractNo, coalesce(Sep2011.BalanceOutstanding,0) as value1, coalesce(Oct2011.BalanceOutstanding,0) as value2, coalesce(Nov2011.BalanceOutstanding,0) as value3

from Sep2011 full join Oct2011

on Sep2011.ContractNo=Oct2011.ContractNo full join Nov2011 on Oct2011.ContractNo=Nov2011.ContractNo;

quit;

 

I get the Values merged for all three databases.  The only issue appears to be that several accounts that appear only in one database(Sep2011) show duplicate values in Value1.  Not sure why that would be.

novinosrin
Tourmaline | Level 20

Can you plz revise your input sample and post again? So I can work on that from my end plz

KevinL
Fluorite | Level 6
I’ll have a look at everything and see if I can find the problem and will
let you know tomorrow. It’s late here.

Thanks again for everything!
novinosrin
Tourmaline | Level 20

Ok , You can always start a new thread to make a question precise and can expect precise answers which will make it comprehensive. Have a good rest of your day

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1291 views
  • 1 like
  • 2 in conversation