Full outer join - issue with listing the linked variable using Coalesce

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 95
Accepted Solution

Full outer join - issue with listing the linked variable using Coalesce

hi -

 

I need to join two tables without losing any of the key variables from both tables. I'm looking to achieve this using proc sql full outer join.

The problem I am trying to solve is that I want the common variable to both tables, to appear as one column. I heard that the 'coalesce' function, can achieve this, however when I tried, the observations from the variable is listed as a binary (either 0 or 1).

 

Base SAS code that achieves result I am looking to get, :

 

 

data want; 
   merge A B; 
      by ID;
        run;

SQL code I used along with the coalesce function, which gives me binary result:

 

 

proc sql;  create table Want as
  select coalesce(a.ID=b.ID) as ID, a.expi, b.sta_dt, b.ts_acn_sts_cd, b.ts_acn_sts_rsn_cd
         from A as a full outer join B as b  on
             a.ID=b.ID; 
                 quit;

To note: There are no blank variables in either table A or B, and my ID column (from both tables) is formatted as 11 text characters.

 

I look forward to receiving your suggestion, thanks


Accepted Solutions
Solution
‎07-13-2016 09:36 AM
Occasional Contributor
Posts: 19

Re: Full outer join - issue with listing the linked variable using Coalesce

Hi
Try changing the coalesce function to:
coalesce(a.id, b.id)
The way it works is to use the first non-missing value in the variables listed.
How you have coded it will compare the values of the two variables, hence the binary result.
Hope this helps.
Cheers
Chris

View solution in original post


All Replies
Solution
‎07-13-2016 09:36 AM
Occasional Contributor
Posts: 19

Re: Full outer join - issue with listing the linked variable using Coalesce

Hi
Try changing the coalesce function to:
coalesce(a.id, b.id)
The way it works is to use the first non-missing value in the variables listed.
How you have coded it will compare the values of the two variables, hence the binary result.
Hope this helps.
Cheers
Chris
Frequent Contributor
Posts: 95

Re: Full outer join - issue with listing the linked variable using Coalesce

Posted in reply to rivieralad

Thanks for the explanation Chris, it is working now!

 

David

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 831 views
  • 1 like
  • 2 in conversation