topic Re: How to update values with multiple columns by using PROC SQL? in SAS Procedures

How to update values with multiple columns by using PROC SQL?

ybz12003 — Tue, 14 Feb 2017 19:13:40 GMT

Hello,

I am learning using PROC SQL to replace the data step for data extraction. I use PROC SQL / UPDATE statement, I found that I didn't update my data correctly, I lost some portion of my original ones.

PROC SQL;
update work, test
set
marital=case when marital in (2, 4) then 2 end,
race=case when race in (3,5,6,7,8) then 7
when race in (4) then 9 end;
quit;

As showing above, I got "martial 2 and 4" changing to 2, but missing 1 and 3 original categories in the updated data. The same as race, I only got the 7 and 9 in race columns, but missing 1 and 2 original rows in the updated table. How to avoid this problem? Please advice. Thanks.

Re: How to update values with multiple columns by using PROC SQL?

Reeza — Tue, 14 Feb 2017 20:38:26 GMT

Your case statements logic is incorrect, ALWAYS read your log. You should see the following NOTE and if you read the last sentence it explains your situation. Add the ELSE clause so that the value stays the same if you don't change it.

397
398 proc sql;
399 update class
400 set age=case when age < 10 then 5 end;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will
result in a missing value for the CASE expression.
NOTE: 19 rows were updated in WORK.CLASS.

401 quit;
NOTE: PROCEDURE SQL used (Total process time):

real time 0.06 seconds
cpu time 0.01 seconds

Re: How to update values with multiple columns by using PROC SQL?

PGStats — Tue, 14 Feb 2017 20:39:27 GMT

The case expression must supply every possibility. In the absence of an else clause, cases that are not covered result in a missing value. So, replace

marital=case when marital in (2, 4) then 2 end

with

marital=case when marital in (2, 4) then 2 else marital end

and so on.