topic Re: merging databases with multiple variables in SAS Procedures

merging databases with multiple variables

KevinL — Tue, 29 May 2018 18:16:23 GMT

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!

Re: merging databases with multiple variables

novinosrin — Tue, 29 May 2018 18:27:55 GMT

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;

Re: merging databases with multiple variables

KevinL — Tue, 29 May 2018 19:15:11 GMT

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?

Re: merging databases with multiple variables

novinosrin — Tue, 29 May 2018 19:18:26 GMT

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

COALESCE(Sep2011.ContractNo,Oct2011.ContractNo)

with

COALESCEC(Sep2011.ContractNo,Oct2011.ContractNo)

Re: merging databases with multiple variables

KevinL — Tue, 29 May 2018 19:44:38 GMT

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?

Re: merging databases with multiple variables

novinosrin — Tue, 29 May 2018 19:46:02 GMT

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

Re: merging databases with multiple variables

KevinL — Tue, 29 May 2018 19:55:34 GMT

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!

Re: merging databases with multiple variables

novinosrin — Tue, 29 May 2018 20:00:17 GMT

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

Re: merging databases with multiple variables

KevinL — Tue, 29 May 2018 20:11:03 GMT

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;

Re: merging databases with multiple variables

novinosrin — Tue, 29 May 2018 20:15:54 GMT

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

Re: merging databases with multiple variables

KevinL — Tue, 29 May 2018 20:31:45 GMT

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

proc sql;

create table want as

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.

Re: merging databases with multiple variables

novinosrin — Tue, 29 May 2018 20:43:43 GMT

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

Re: merging databases with multiple variables

KevinL — Tue, 29 May 2018 20:47:15 GMT

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!

Re: merging databases with multiple variables

novinosrin — Tue, 29 May 2018 20:54:39 GMT

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