topic Re: Update statement error I don't understand in SAS Programming

Update statement error I don't understand

noetsi — Mon, 23 Jun 2025 21:03:27 GMT

Baffled why this error is occurring.

PROC SQL;
CREATE TABLE WORK.updatet1 AS
SELECT t1.TimeFrame ,
t1.'Counseling on Enrollment Opportu'n as CEO,
t1.'Instruction in Self Advocacy'n as ISA,
t1.'Job Exploration Counseling'n as JEC,
t1.'Work Based Learning Experience -'n as WBLE,
t1.'Workplace Readiness Training'n as WRT
FROM WORK.updatet t1;
QUIT;

PROC SQL;
   CREATE TABLE WORK.revisedp1 AS 
   Select * from revisedp ;
   update WORK.revisedp1 as u
   set u.amount=(select CEO from updatet1 n where u.timeframe =n.timeframe)
   where u.rsaservicecategory = 'Counseling on Enrollment Opportu';
   quit;

I get ERROR 73-322: Expecting an =. which I think is for invalid variable names, not sure why these are. I tried it in the original names and got the same error.

PROC SQL;
CREATE TABLE WORK.revisedp1 AS
Select * from revisedp ;
update WORK.revisedp1 as u
set u.amount=(select 'Counseling on Enrollment Opportu'n from updatet n where u.timeframe =n.timeframe)
where u.rsaservicecategory = 'Counseling on Enrollment Opportu';
quit;

The table being updated has am amount by month(timeframe) for a series of categories. We are updating it with a table that looks like this (I can't show any of the results because it is state data and even aggregates can not be posted).

Re: Update statement error I don't understand

PaigeMiller — Mon, 23 Jun 2025 22:33:27 GMT

Please provide the ENTIRE log for this PROC SQL. (We don't want 100s of lines before or after this PROC SQL, just the log for this PROC SQL)

Please copy the log for this PROC SQL as text and paste it as text into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

Re: Update statement error I don't understand

Tom — Mon, 23 Jun 2025 23:02:46 GMT

A couple of things I see you should check (but they shouldn't cause that error message).

In some places you use WORK.updatet1 and other places you use just updatet1 . Normally both should mean the same dataset , but if you have a USER libref (or another libref and have set to the USER= system option to that other libref) then they won't mean the same dataset.

You reference the variable CEO without saying which of the two datsets referenced in the query it should come from.

Are you sure the value of the variable u.rsaservicecategory has the truncated string 'Counseling on Enrollment Opportu' ? But that shouldn't give an error, just incorrect results.

More likely because of having to type all of those name literals you have gotten your SAS session confused with unbalanced quotes. Try resetting the SAS session and running the code again.

Note that you can use a simple RENAME statement or RENAME= dataset option to fix the variable names without having to make a separate dataset.

...
FROM WORK.updatet
  (rename=(
  'Counseling on Enrollment Opportu'n = CEO
  'Instruction in Self Advocacy'n = ISA
  'Job Exploration Counseling'n = JEC
  'Work Based Learning Experience -'n = WBLE
  'Workplace Readiness Training'n = WRT
  ))
...

Re: Update statement error I don't understand

noetsi — Tue, 24 Jun 2025 16:32:58 GMT

I tried running it from scratch today and got the same error.

This is what the log says:

28         PROC SQL;
29            CREATE TABLE WORK.updatet1 AS
30            SELECT t1.TimeFrame ,
31                   t1.'Counseling on Enrollment Opportu'n as CEO,
32                   t1.'Instruction in Self Advocacy'n as ISA,
33                   t1.'Job Exploration Counseling'n as JEC,
34                   t1.'Work Based Learning Experience -'n as WBLE,
35                   t1.'Workplace Readiness Training'n as WRT
36               FROM WORK.updatet t1;
NOTE: Table WORK.UPDATET1 created, with 23 rows and 6 columns.

37         QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

38         
39         PROC SQL;
40            CREATE TABLE WORK.revisedp1 AS
41            Select * from revisedp ;
NOTE: Table WORK.REVISEDP1 created, with 656 rows and 3 columns.

42            update WORK.revisedp1 as u
43            set u.amount=(select CEO from updatet1 n where u.timeframe =n.timeframe)
                   _
                   73
                   76
ERROR 73-322: Expecting an =.

2                                                          The SAS System                               12:27 Tuesday, June 24, 2025

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

44            where u.rsaservicecategory = 'Counseling on Enrollment Opportu';
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
45            quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
46         
47         
48         
49         


50         PROC SQL;
51            CREATE TABLE WORK.revisedp1 AS
52            Select * from revisedp ;
NOTE: Table WORK.REVISEDP1 created, with 656 rows and 3 columns.

53            update WORK.revisedp1 as u
54            set u.amount=(select 'Counseling on Enrollment Opportu'n from updatet n where u.timeframe =n.timeframe)
                   _
                   73
                   76
ERROR 73-322: Expecting an =.

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

55            where u.rsaservicecategory = 'Counseling on Enrollment Opportu';
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
56            quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: Update statement error I don't understand

noetsi — Tue, 24 Jun 2025 16:41:54 GMT

Doing that now

Re: Update statement error I don't understand

Kurt_Bremser — Tue, 24 Jun 2025 19:54:33 GMT

Use a single-level name for the column, without a prefixed table name or alias.

Re: Update statement error I don't understand

noetsi — Tue, 24 Jun 2025 20:04:37 GMT

I am sorry, I don't understand what you mean by this?

Re: Update statement error I don't understand

PaigeMiller — Tue, 24 Jun 2025 20:49:32 GMT

43            set u.amount=(select CEO from updatet1 n where u.timeframe =n.timeframe)
                   _
                   73
                   76
ERROR 73-322: Expecting an =.

Use amount, not u.amount

Re: Update statement error I don't understand

Kurt_Bremser — Tue, 24 Jun 2025 20:54:56 GMT

u.amount

Two-level name

amount

Single-level name, just like

would be, and that's why the dot is indicated as the location of the ERROR

Re: Update statement error I don't understand

noetsi — Tue, 24 Jun 2025 22:15:37 GMT

Thank you very much.

Re: Update statement error I don't understand

noetsi — Tue, 24 Jun 2025 22:48:37 GMT

That worked thank you very much. I am entirely baffled why this matters, but it removed the problem. Thanks again.

Re: Update statement error I don't understand

Kurt_Bremser — Tue, 24 Jun 2025 22:54:52 GMT

In the SET clause of the UPDATE statement, only variables of the dataset named in UPDATE can be set, so no identification of a specific table is needed and therefore not recognized by the SQL compiler.

Re: Update statement error I don't understand

noetsi — Wed, 25 Jun 2025 15:45:37 GMT

Thanks I appreciate your explanation. I usually use SAS for statistics and do SQL in the microsoft server which is the primary place our system was set up to access our data. There are some nice features of SAS sql so I am learning it more, but update's are one of the places these two platforms vary significantly differently. Thank you a lot for your help.

Re: Update statement error I don't understand

Tom — Wed, 25 Jun 2025 21:57:08 GMT

I have rarely found the UPDATE statement of PROC SQL very useful for data analysis.

I always suspected that is something that DATABASE builders might find useful. But for building analysis datasets normal data steps and/or SQL joins is usually the simplest thing to use.

Re: Update statement error I don't understand

noetsi — Thu, 26 Jun 2025 21:03:42 GMT

We had a problem in that spending in one category had to be assigned to other categories on what was essentially a best guess approach. So I built excel tables with our best estimate of what the change was and then we used these to update the original. I am sure there were other ways to do this in SAS (in general I have found there are nearly always more than one way to do something in SAS). 🙂 Thanks again for the help.

Re: Update statement error I don't understand

Tom — Fri, 27 Jun 2025 01:20:44 GMT

Normally in that case you would use MERGE statement in a data step.

data fixed;
  merge  original updates;
  by key vars ;
run;

Although you might want to use the orignal UPDATE statement (that has nothing to do with the SQL statement with the same name). That is designed to apply transactions. That way you only need to enter the values that change in the transaction dateset since with UPDATE missing values do not replace the original value. So you only need to enter the values that need to change.