BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee
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;

View solution in original post

7 REPLIES 7
FriedEgg
SAS Employee
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;
FriedEgg
SAS Employee
This also appears to be directly related to another post you made a few hours ago. You should keep you follow up questions attached to the original thread...
ybz12003
Rhodochrosite | Level 12

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.

FriedEgg
SAS Employee
Yes, that is what it means, which is fine, if that's the functionality you want. I will assume it not. So you would want to add and:

else AGE/EDUCATION before the end on your two case statements
rogerjdeangelis
Barite | Level 11
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;

rogerjdeangelis
Barite | Level 11
Looks like I was wrong
ybz12003
Rhodochrosite | Level 12

That is why I didn't use "else" in the AGE statement.  I was afraid that it will assign the "." into the 3 too.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5137 views
  • 3 likes
  • 3 in conversation