Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 297
Accepted Solution

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

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.

 


Accepted Solutions
Solution
‎02-15-2017 03:38 PM
Respected Advisor
Posts: 4,641

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

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.

PG

View solution in original post


All Replies
Super User
Posts: 17,750

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

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

 

 

 

 

 

Solution
‎02-15-2017 03:38 PM
Respected Advisor
Posts: 4,641

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

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.

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 803 views
  • 1 like
  • 3 in conversation