Update statement error I don't understand

[noetsi]

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

[PaigeMiller]

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.

[noetsi]

Doing that now

[Tom]

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

[noetsi]

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

[Kurt_Bremser]

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

[noetsi]

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

[PaigeMiller]

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

[noetsi]

Thank you very much.

[Kurt_Bremser]

u.amount

Two-level name

amount

Single-level name, just like

u

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

[noetsi]

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

[Kurt_Bremser]

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.

[noetsi]

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.

[Tom]

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.

[noetsi]

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.