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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.