Conditional Coalesce function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Conditional Coalesce function

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

  1. A.User_ID,

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


Accepted Solutions
Solution
‎06-10-2013 01:44 PM
Super User
Posts: 19,789

Re: Conditional Coalesce function

Posted in reply to 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

  1. A.User_ID,

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;

View solution in original post


All Replies
Solution
‎06-10-2013 01:44 PM
Super User
Posts: 19,789

Re: Conditional Coalesce function

Posted in reply to 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

  1. A.User_ID,

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;

Super User
Super User
Posts: 7,039

Re: Conditional Coalesce function

Posted in reply to saslearner2

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;

Occasional Contributor
Posts: 5

Re: Conditional Coalesce function

Thanks

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 340 views
  • 4 likes
  • 3 in conversation