Hello:
I got this error message from the log, Please advice how to fix it, thank you.
640 PROC SQL;
641 update work.test
642 set age=case when age in (1,2,3,4,5) then 1
643 when age in (6,7,8,9) then 2
644 when age in (10,11,12,13) then 3 end,
645 set EDUCATION=case when EDUCATION in (1,2) then 1
---------
73
76
ERROR 73-322: Expecting an =.
ERROR 76-322: Syntax error, statement will be ignored.
646 when EDUCATION in (3,4) then 2
647 when EDUCATION in (5.6) then 3 end;
648 quit;
PROC SQL;
update work.test
set
age=case when age in (1,2,3,4,5) then 1
when age in (6,7,8,9) then 2
when age in (10,11,12,13) then 3 end,
EDUCATION=case when EDUCATION in (1,2) then 1
when EDUCATION in (3,4) then 2
when EDUCATION in (5.6) then 3 end;
quit;
PROC SQL;
update work.test
set
age=case when age in (1,2,3,4,5) then 1
when age in (6,7,8,9) then 2
when age in (10,11,12,13) then 3 end,
EDUCATION=case when EDUCATION in (1,2) then 1
when EDUCATION in (3,4) then 2
when EDUCATION in (5.6) then 3 end;
quit;
I got a note as shown below. Does this mean I have to else in the "case...when"?
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: 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.
This does not answer you question, however my limited knowledge makes me believe.
1. Relational: SQL tables generally have a unique key that enable updates
2, set must resolve to one value per key
3. You need a relational object for your case statement to operate on
SAS: Using proc sql to update missing values
Change missing ages to 99 using a transaction dataset
HAVE MASTER and TRANSACTION datsets
====
Up to 40 obs WORK.MASTER total obs=19
Obs NAME SEX AGE
1 Alfred M .
2 Alice F .
3 Barbara F .
4 Carol F .
5 Henry M .
6 James M 12
7 Jane F 12
8 Janet F 15
9 Jeffrey M .
10 John M 12
11 Joyce F 11
12 Judy F .
13 Louise F 12
14 Mary F 15
15 Philip M 16
16 Robert M 12
17 Ronald M 15
18 Thomas M 11
19 William M 15
Up to 40 obs WORK.TRANSACTION total obs=10
Obs NAME SEX AGE
1 Alfred M 14
2 Alice F 13
3 Barbara F 13
4 Carol F 14
5 Henry M 14
6 James M 12
7 Jane F 12
8 Janet F 15
9 Jeffrey M 13
10 John M 12
WANT New master
================
Up to 40 obs from master total obs=19
Obs NAME SEX AGE
1 Alfred M 99
2 Alice F 99
3 Barbara F 99
4 Carol F 99
5 Henry M 99
6 James M 12
7 Jane F 12
8 Janet F 15
9 Jeffrey M 99
10 John M 12
11 Joyce F 11
12 Judy F . Not updated because only first 10
are in the transaction dataset
13 Louise F 12
14 Mary F 15
15 Philip M 16
16 Robert M 12
17 Ronald M 15
18 Thomas M 11
19 William M 15
SOLUTION
========
SAS
===
data master;
set sashelp.class(keep=name sex age);
if age in (13,14) then age=.;
run;quit;
data transaction;
set sashelp.class(obs=10 keep=name sex age);
run;quit;
proc sql;
select * from master;
update master as a
set age=(
select
case
when b.age in (13,14) then 99
else a.age
end as age
from
transaction as b
where
a.name=b.name)
where exists (select * from transaction as b where a.name=b.name );
select * from master;
quit;
That is why I didn't use "else" in the AGE statement. I was afraid that it will assign the "." into the 3 too.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.