I have an odder error that I haven't ran into in the past. This has always worked for me in previous instances so the failure makes me wonder if it is a setting at my new job. I attempted to run this code: proc sql;
connect to odbc (user="&ntuser." password="&ntpw." dsn=Server_Name connection=shared);
create table End_Result as
select * from connection to odbc
(
with EOM_date as
(
select
max(Prior_time_day_gen_id) as max_procdt
from Business_Calander_Table
where month(cast(prior_time_day_gen_id as date)) != month(cast(time_day_gen_id as date))
and cast(prior_time_day_gen_id as date) <= &start_db.
)
select
a.*
from
Data_Table a inner join Eom_Date
on a.procdt = b.max_procdt
);
quit; I got the error: ERROR: PROC SQL requires any created table to have at least 1 column. If I run this code directly in the SQL Server it works just fine, and if I instead run the code below it works (this code takes the with statement and puts it as a sub-select instead: proc sql;
connect to odbc (user="&ntuser." password="&ntpw." dsn=Server_Name connection=shared);
create table End_Result as
select * from connection to odbc
(
select
a.*
from
Data_Table a
inner join (
select
max(Prior_time_day_gen_id) as max_procdt
from Business_Calander_Table
where month(cast(prior_time_day_gen_id as date)) != month(cast(time_day_gen_id as date))
and cast(prior_time_day_gen_id as date) <= &start_db.
)
on a.procdt = b.max_procdt
);
quit; Any ideas on why it is failing on the with statement? Using 9.4 on Windows 10. The server is a MS SQL Server.
... View more