topic Update statement in PROC sql? in SAS Procedures

Update statement in PROC sql?

ybz12003 — Thu, 09 Feb 2017 18:24:49 GMT

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;

Re: Update statement in PROC sql?

FriedEgg — Thu, 09 Feb 2017 18:34:45 GMT

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;

Re: Update statement in PROC sql?

FriedEgg — Thu, 09 Feb 2017 18:36:02 GMT

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...

Re: Update statement in PROC sql?

ybz12003 — Thu, 09 Feb 2017 18:45:36 GMT

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.

Re: Update statement in PROC sql?

FriedEgg — Thu, 09 Feb 2017 18:49:08 GMT

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

Re: Update statement in PROC sql?

rogerjdeangelis — Thu, 09 Feb 2017 20:31:07 GMT

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;

Re: Update statement in PROC sql?

rogerjdeangelis — Thu, 09 Feb 2017 20:31:59 GMT

Looks like I was wrong

Re: Update statement in PROC sql?

ybz12003 — Thu, 09 Feb 2017 20:43:05 GMT

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