Help using Base SAS procedures

Proc SQL: Error when using UPDATE

Reply
Frequent Contributor
Posts: 142

Proc SQL: Error when using UPDATE

Hi I'm getting an error message I don't understand when I try to UPDATE a table. Here is my update query:

proc sql;

     update  tvtable

     set  FullyLoaded_estbd_dt =

     max(HBO_estbd_dt,

         Cinemax_estbd_dt,

         Showtime_estbd_dt,

      STARZ_estbd_dt,

      Encore_estbd_dt,

      TMC_estbd_dt,

      EPIX_estbd_dt)

     where FullyLoaded_estbd_dt = '31DEC9999'd

     and HBO_estbd_dt       < '31DEC9999'd

     and Cinemax_estbd_dt   < '31DEC9999'd

     and Showtime_estbd_dt  < '31DEC9999'd

     and STARZ_estbd_dt     < '31DEC9999'd

     and Encore_estbd_dt    < '31DEC9999'd

     and TMC_estbd_dt       < '31DEC9999'd

     and EPIX_estbd_dt      < '31DEC9999'd;

quit;


Here is the error message  im getting:


ERROR: File WORK.tvtable cannot be updated because its encoding does not match the

       session encoding or the file is in a format native to another host, such as

       SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64.

Did I not write the update query correctly?

Any feedback is greatly appreciated. Thanks!

Super User
Posts: 10,490

Re: Proc SQL: Error when using UPDATE

I don't see a source of the update dataset, i.e. a From for the values you are getting the MAX of, should be one just before WHERE

Frequent Contributor
Posts: 142

Re: Proc SQL: Error when using UPDATE

Thanks. I'm updating the table from the same table.

proc sql;

     update  tvtable

     set  FullyLoaded_estbd_dt =

     max(HBO_estbd_dt,

         Cinemax_estbd_dt,

         Showtime_estbd_dt,

      STARZ_estbd_dt,

      Encore_estbd_dt,

      TMC_estbd_dt,

      EPIX_estbd_dt)

    from tvtable

     where FullyLoaded_estbd_dt = '31DEC9999'd

     and HBO_estbd_dt       < '31DEC9999'd

     and Cinemax_estbd_dt   < '31DEC9999'd

     and Showtime_estbd_dt  < '31DEC9999'd

     and STARZ_estbd_dt     < '31DEC9999'd

     and Encore_estbd_dt    < '31DEC9999'd

     and TMC_estbd_dt       < '31DEC9999'd

     and EPIX_estbd_dt      < '31DEC9999'd;

quit;



when I add in the "from: its still not working.


From tvtable

           ----

           22

           76

ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, WHERE,

              ||.

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

Contributor
Posts: 26

Re: Proc SQL: Error when using UPDATE

Try using a SELECT in front of the Max(). You're using a FROM and WHERE, presumably trying to make an sql expression

Frequent Contributor
Posts: 142

Re: Proc SQL: Error when using UPDATE

Thanks. stillgetting error messages.

184

185  proc sql;

186       update  tvtable

187        set  FullyLoaded_estbd_dt =

188       select

189       max(HBO_estbd_dt,

                          -

                          79

ERROR 79-322: Expecting a (.

190           Cinemax_estbd_dt,

191           Showtime_estbd_dt,

192           STARZ_estbd_dt,

193           Encore_estbd_dt,

194           TMC_estbd_dt,

195           EPIX_estbd_dt)

196       From tvtable

          ----

          22

          76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=,

              <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE,

              LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

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

Super User
Super User
Posts: 7,401

Re: Proc SQL: Error when using UPDATE

You need brackets around the subquery:

proc sql;

     update  tvtable

     set  FullyLoaded_estbd_dt =(select max(HBO_estbd_dt,

                                                              Cinemax_estbd_dt,

                                                              Showtime_estbd_dt,

                                                              STARZ_estbd_dt,

                                                               ...

                                                    From tvtable);

quit;

Super User
Posts: 9,676

Re: Proc SQL: Error when using UPDATE

From your Log, it said your table is a different version table with your current sas session. Maybe you should transform it into your local version sas table via

proc cport+ proc cimport   , proc immgrate , dataset option outrep=  .

Valued Guide
Posts: 3,208

Re: Proc SQL: Error when using UPDATE

I do not know what you have done to get the data work.tvtable there.
It is looking that table has been created by a binary upload (using eg Windows explorer) or using an other session (utf8/latin1) for the same location.

By that you SAS-dataset is not anymore of native type but of foreign type, by this updating in place (modify) is not possible anymore.

To correct that go back and create your work.tctable in a native type that is create that using your current sas version and sas encoding.

Moving and Accessing SAS(R) 9.4 Files, Second Edition  (CEDA limitations)

http://blogs.sas.com/content/sasdummy/2010/05/24/finding-the-foreigners-in-your-sas-environment/

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 7 replies
  • 662 views
  • 3 likes
  • 6 in conversation