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

I am trying to modify a value in a field in one data set based on criteria for this and another data set.  Here is my current code, which doesn't work.  My code should change the hazard code from MO to WM in table B where the claim number is the same between the two tables and there are values in the first table for both loss causes WM and WI. 

proc sql;

UPDATE TABLE_B AS B

HAZARD_CODE_INDICATOR = 'WM'

FROM TABLE_A AS A

where

A.CLM_NBR = B.CLM_NBR AND

A.LOSS_CAUSE = 'MO' AND

(B.HAZARD_CODE_INDICATOR = 'WM' AND

B.VALUES <> 0) AND

(B.HAZARD_CODE_INDICATOR = 'WI' AND

B.VALUES <> 0));

quit;

 

Here are the error messages I receive:

 

23 proc sql;

24 UPDATE TABLE_B AS B

25 HAZARD_CODE_INDICATOR = 'WM'

_____________________

79

26 FROM FROM TABLE_A AS A

____ __

22 76

202

ERROR 79-322: Expecting a SET.

ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, WHERE, ||.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

26 ! FROM FROM TABLE_A AS A

__

22

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,

CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

 

 

Thank you for any help you can provide,

Christine

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@Seabird

The following should work.

 

data table_a;
  CLM_NBR=1;
  LOSS_CAUSE = 'MO';
  HAZARD_CODE_INDICATOR='WM';
  output;
  stop;
run;

data table_b;
  CLM_NBR=1;
  HAZARD_CODE_INDICATOR = 'WI';
  VALUES=1;
  output;
  CLM_NBR=1;
  HAZARD_CODE_INDICATOR = 'XX';
  VALUES=1;
  output;
  stop;
run;

proc sql;
  UPDATE TABLE_B AS B
    set HAZARD_CODE_INDICATOR = 'WM'
  where 
    B.HAZARD_CODE_INDICATOR = 'WI' AND
    B.VALUES <> 0 AND
    exists
  	(
  		select *
  		from table_a as a
  	    where
  	      A.CLM_NBR = B.CLM_NBR AND
  	      A.LOSS_CAUSE = 'MO' AND
  	      A.HAZARD_CODE_INDICATOR = 'WM'
  	)	
	;
quit;

 

 

View solution in original post

3 REPLIES 3
Reeza
Super User

I don't think SAS SQL supports updates with a join using SQL.

 

Try creating a new table using SQL rather than updating the table.

Patrick
Opal | Level 21

@Seabird

The following should work.

 

data table_a;
  CLM_NBR=1;
  LOSS_CAUSE = 'MO';
  HAZARD_CODE_INDICATOR='WM';
  output;
  stop;
run;

data table_b;
  CLM_NBR=1;
  HAZARD_CODE_INDICATOR = 'WI';
  VALUES=1;
  output;
  CLM_NBR=1;
  HAZARD_CODE_INDICATOR = 'XX';
  VALUES=1;
  output;
  stop;
run;

proc sql;
  UPDATE TABLE_B AS B
    set HAZARD_CODE_INDICATOR = 'WM'
  where 
    B.HAZARD_CODE_INDICATOR = 'WI' AND
    B.VALUES <> 0 AND
    exists
  	(
  		select *
  		from table_a as a
  	    where
  	      A.CLM_NBR = B.CLM_NBR AND
  	      A.LOSS_CAUSE = 'MO' AND
  	      A.HAZARD_CODE_INDICATOR = 'WM'
  	)	
	;
quit;

 

 

Seabird
Calcite | Level 5

Thank you very much, Patrick!  You saved my bacon!  I've been struggling with this for a while and was nearing a deadline.

 

Seabird

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 813 views
  • 2 likes
  • 3 in conversation