topic Re: Conditional Coalesce function in SAS Programming

Conditional Coalesce function

saslearner2 — Mon, 10 Jun 2013 17:34:04 GMT

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

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

Re: Conditional Coalesce function

Reeza — Mon, 10 Jun 2013 17:44:41 GMT

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

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;

Re: Conditional Coalesce function

Tom — Mon, 10 Jun 2013 18:12:01 GMT

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;

Re: Conditional Coalesce function

saslearner2 — Wed, 17 Jul 2013 03:22:07 GMT

Thanks