BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saslearner2
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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;

Tom
Super User Tom
Super User

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;

saslearner2
Calcite | Level 5

Thanks

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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