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
Thanks for the explanation Chris, it is working now!
David
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.
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.