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!
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
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.
Try using a SELECT in front of the Max(). You're using a FROM and WHERE, presumably trying to make an sql expression
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.
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;
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= .
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/
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.
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.