BookmarkSubscribeRSS Feed
Mgarret
Obsidian | Level 7

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!

7 REPLIES 7
ballardw
Super User

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

Mgarret
Obsidian | Level 7

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.

rcwright
Calcite | Level 5

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

Mgarret
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

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

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1972 views
  • 3 likes
  • 6 in conversation