Hi Team,
I am trying to create a code to join two tables, where the value of few variables needs to be replaced from table2 and if it is not available in table2 then need to retain the value from the existing table1.
I understand this can be done using coalesce function. However I may not require to replace all the variables that’s why want to trigger the coalesce function only to the variables where I select ‘Y’ in the % let statements otherwise original value form table1 should come in the result.
So far I have created this piece of code, but now looking for suggestions to apply if conditions in the proc sql statements based on the above declared values in % let option in all the variables.
%LET Field1='Y';
%LET Field2='Y';
%LET Field3='Y';
%LET Field4='Y';
%LET Field5='Y';
PROC SQL;
CREATE TABLE NEW_DATA AS
SELECT
COALESCE(B.Field1, A.Field1) AS Field1,
COALESCE(B.Field2, A.Field2) AS Field2,
COALESCE(B.Field3, A.Field3) AS Field3,
COALESCE(B.Field4, A.Field4) AS Field4,
COALESCE(B.Field5, A.Field5) AS Field5
FROM table1 AS A
LEFT JOIN table2 AS B
ON table1.User_ID = table2.User_ID;
QUIT;
thanks, saslearner2
Add in case statements? Or wrap it all into a macro and use conditional put statements.
%LET Field1='Y';
%LET Field2='Y';
%LET Field3='Y';
%LET Field4='Y';
%LET Field5='Y';
PROC SQL;
CREATE TABLE NEW_DATA AS
SELECT
case when &field1="Y" then COALESCE(B.Field1, A.Field1)
else a.Field1 AS Field1,
FROM table1 AS A
LEFT JOIN table2 AS B
ON table1.User_ID = table2.User_ID;
QUIT;
Add in case statements? Or wrap it all into a macro and use conditional put statements.
%LET Field1='Y';
%LET Field2='Y';
%LET Field3='Y';
%LET Field4='Y';
%LET Field5='Y';
PROC SQL;
CREATE TABLE NEW_DATA AS
SELECT
case when &field1="Y" then COALESCE(B.Field1, A.Field1)
else a.Field1 AS Field1,
FROM table1 AS A
LEFT JOIN table2 AS B
ON table1.User_ID = table2.User_ID;
QUIT;
Why not just use UPDATE statement?
data new_data ;
update table1 table2 ;
by user_id;
run;
If you only want to update specific fields that are in TABLE2 then add a KEEP= dataset option.
data new_data ;
update table1 table2(keep=user_id field1 field2 field3) ;
by user_id;
run;
Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.