topic Re: Updating value in multiple columns in Proc SQL? in SAS Procedures

Updating value in multiple columns in Proc SQL?

ybz12003 — Thu, 09 Feb 2017 15:10:07 GMT

Hello:

I try to update the multiple values in different columns. I wrote a code below, The log shows in the Syntax error. Please help. Thanks.

338 PROC SQL;
339 update work.test
340 set age=age
341 case
----
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, !!, (, *, **, +, ',', -, '.', /,
WHERE, ||.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

342 when age in (1,2,3,4,5) then age=1
----
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, &, AND, OR, |.

ERROR 76-322: Syntax error, statement will be ignored.

343 when age in (6,7,8,9) then age=2
344 when age in (10,11,12,13) then age=3
345 end;
346 , set marital=marital
-
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

347 case
348 when marital in (2, 4) then marital=2
349 end;
350 , set education=education
-
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

351 case
352 when education in (1,2) then education=1
353 when education in (3,4) then education=2
354 when education in (5.6) then education=3
355 end;
356 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

Re: Updating value in multiple columns in Proc SQL?

ybz12003 — Thu, 09 Feb 2017 15:07:56 GMT

My original codes is list below:

PROC SQL;
update work.test
set age=age
case
when age in (1,2,3,4,5) then age=1
when age in (6,7,8,9) then age=2
when age in (10,11,12,13) then age=3
end;
, set marital=marital
case
when marital in (2, 4) then marital=2
end;
, set education=education
case
when education in (1,2) then education=1
when education in (3,4) then education=2
when education in (5.6) then education=3
end;
quit;

Re: Updating value in multiple columns in Proc SQL?

RW9 — Thu, 09 Feb 2017 15:39:01 GMT

Am afraid there is several problems with that code, semicolons all over the place, case statements invalid, assignments wrong, no from, and update doesn't work like that anyways. Too much to waste time fixing. Why not just do a very simple datastep:

data want;
  set have;
  select(age);
    when (1,2,3,4,5) age=1;
    when (6,7,8,9) age=2;
    otherwise age=3;
  end;
...
run;

Alternatively post some test data in the form of a datastep so that we have something to work with

Re: Updating value in multiple columns in Proc SQL?

ybz12003 — Thu, 09 Feb 2017 15:53:55 GMT

Thanks for your advice. I'm trying to learn how to change the value in PROC SQL. I have done this in data step.

Re: Updating value in multiple columns in Proc SQL?

RW9 — Thu, 09 Feb 2017 16:14:47 GMT

Well, its probably a good idea to start from the basics then. Your code has lots of problems. This code demonstrates how to to an update - note it will affect all rows as there is no where.

data test;
  set sashelp.class;
run;

proc sql;
  update TEST 
  set AGE=case when AGE in (14,13) then 1
               when AGE in (11,12) then 2
               else 3 end,
      HEIGHT=case when HEIGHT < 63 then 1
                  else 2 end;
quit;

You will see semicolon is only at the end of the SQL. Variables are delimited by commas, set appears only once, case statement is different. These are basic SQL things which you should be learning before trying to do more advanced tasks like updating and joining. (and presenting code in a the code window - {i} and using consistent casing/indetation and such like makes reading code easier).