topic Re: update statement using if statement in SAS Programming

update statement using if statement

kajal_30 — Wed, 25 Sep 2024 18:15:46 GMT

I am trying to update one column in one table using value from another table as below.

Is there a way to update the column only if it has null value or else keep the previous value and do no updates. Please let me know what wrong I am doing in the code below.

proc sql;
	update table customer a
		set a.pre_diags =  
			case
				when  a.pre_diags is null then b.pre_diags 
				else a.pre_diags
			end 
		from retails b;
quit;

Regards

Kajal

Re: update statement using if statement

ChrisNZ — Thu, 26 Sep 2024 18:16:43 GMT

See here.

This works:

data HAVE;
  set SASHELP.CLASS;
  if NAME='Alice' then AGE=.;
run;

proc sql;
  update HAVE a
  set AGE = (select AGE from SASHELP.CLASS b where a.NAME=b.NAME)
  where a.AGE = .;  inner join
quit;

Edit:

Adding that the inner join syntax would be a lot more efficient for large updates, but does not seem to be supported by SAS.

Re: update statement using if statement

Tom — Wed, 25 Sep 2024 20:14:49 GMT

You are testing in the wrong place. Apply the test when selecting which observations to update.

update CUSTOMER a
  set PRE_DIAGS = (......)
  where missing(PRE_DIAGS)
;

Also which of the values of PRE_DIAGS in RETAILS do you want to use to replace the current observations value of PRE_DIAGS in CUSTOMER??

Is there some type of customer id in both datasets that could be used to know where in RETAILS to look? Could the same customer appear more than once in RETAILS? If so which value do you want to use?

update CUSTOMER a
  set PRE_DIAGS = 
    (select max(b.pre_diags) from RETAILS b 
      where a.customer_id  = b.customer_id)
  where missing(PRE_DIAGS)
;