BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brulard
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
rivieralad
Obsidian | Level 7
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

2 REPLIES 2
rivieralad
Obsidian | Level 7
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
brulard
Pyrite | Level 9

Thanks for the explanation Chris, it is working now!

 

David

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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