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.
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.