Help using Base SAS procedures

merging databases with multiple variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

merging databases with multiple variables

[ Edited ]

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!

 


Accepted Solutions
Solution
‎05-29-2018 03:45 PM
PROC Star
Posts: 1,817

Re: merging databases with multiple variables

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


All Replies
Solution
‎05-29-2018 03:45 PM
PROC Star
Posts: 1,817

Re: merging databases with multiple variables

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;
Occasional Contributor
Posts: 7

Re: merging databases with multiple variables

Posted in reply to novinosrin

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?

PROC Star
Posts: 1,817

Re: merging databases with multiple variables

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

COALESCE(Sep2011.ContractNo,Oct2011.ContractNo)

 

with

COALESCEC(Sep2011.ContractNo,Oct2011.ContractNo)

Occasional Contributor
Posts: 7

Re: merging databases with multiple variables

[ Edited ]
Posted in reply to novinosrin

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?

PROC Star
Posts: 1,817

Re: merging databases with multiple variables

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

Occasional Contributor
Posts: 7

Re: merging databases with multiple variables

Posted in reply to novinosrin

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!

PROC Star
Posts: 1,817

Re: merging databases with multiple variables

[ Edited ]

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 

Occasional Contributor
Posts: 7

Re: merging databases with multiple variables

Posted in reply to novinosrin

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;

 

PROC Star
Posts: 1,817

Re: merging databases with multiple variables

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

Occasional Contributor
Posts: 7

Re: merging databases with multiple variables

Posted in reply to novinosrin

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.

PROC Star
Posts: 1,817

Re: merging databases with multiple variables

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

Occasional Contributor
Posts: 7

Re: merging databases with multiple variables

Posted in reply to novinosrin
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!
PROC Star
Posts: 1,817

Re: merging databases with multiple variables

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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