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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.