topic Re: Full outer join - issue with listing the linked variable using Coalesce in SAS Programming

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

brulard — Wed, 13 Jul 2016 12:57:32 GMT

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

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

rivieralad — Wed, 13 Jul 2016 13:04:55 GMT

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

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

brulard — Wed, 13 Jul 2016 13:37:18 GMT

Thanks for the explanation Chris, it is working now!

David