merging databases with multiple variables

[KevinL]

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!

[novinosrin · Accepted solution]

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]

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]

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]

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]

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

[KevinL]

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]

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]

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]

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

[KevinL]

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]

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

[KevinL]

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]

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