topic Re: Proc SQL: Error when using UPDATE in SAS Procedures

Proc SQL: Error when using UPDATE

Mgarret — Thu, 18 Sep 2014 19:54:28 GMT

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!

Re: Proc SQL: Error when using UPDATE

ballardw — Thu, 18 Sep 2014 20:37:22 GMT

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

Re: Proc SQL: Error when using UPDATE

Mgarret — Thu, 18 Sep 2014 20:46:55 GMT

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

----

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

||.

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

Re: Proc SQL: Error when using UPDATE

rcwright — Thu, 18 Sep 2014 21:17:31 GMT

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

Re: Proc SQL: Error when using UPDATE

Mgarret — Thu, 18 Sep 2014 21:27:53 GMT

Thanks. stillgetting error messages.

184

185 proc sql;

186 update tvtable

187 set FullyLoaded_estbd_dt =

188 select

189 max(HBO_estbd_dt,

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

----

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.

Re: Proc SQL: Error when using UPDATE

Ksharp — Fri, 19 Sep 2014 12:41:20 GMT

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

Re: Proc SQL: Error when using UPDATE

RW9 — Fri, 19 Sep 2014 12:49:35 GMT

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;

Re: Proc SQL: Error when using UPDATE

jakarman — Fri, 19 Sep 2014 15:33:41 GMT

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/