Proc SQL Error: with table failure

[ksnyder1986]

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.

[RW9]

The syntax with is not valid ANSI SQL, it is a database specific functionality.  hence it is not implemented in proc sql as that only implements ANSI SQL.  If you want to use that syntax, then you will need to use pass-through and send the code to the specific database for processing.

[ksnyder1986]

I'm sorry, but isn't this a pass-through syntax? I am using an odbc connection to the server. Perhaps I misunderstand exactly what a pass-through means.

Also, this syntax worked correctly at my previous job in the same type of statement.

[RW9]

True, you are using pass-through there, but not in a way that SAS understands as it is database specific.  From what I can tell from the with syntax, it doesn't create or return a table, but more of a reference to one.  SAS expects a table back from the statement.  What is the purpose in trying to use the db specific functionality like that?  A simple select should suffice - i.e. your not going to get any benefit from the syntax even if it did work.

[ksnyder1986]

This example is a bit of a simple one. There are some more complex SQL solutions that I had at the previous job that abused creating a series of cascading "with statements" to use some of the functionality that SQL has that SAS doesn't, mainly multiple data partitioning followed by additional selects.

[RW9]

Well, to my mind, if you need to use database specific functionality like that, then code on the database.  Otherwise extract the data to SAS and process the data there.  You will find partitioning data is pretty easy in SAS - its a different way of thinking of the data.  So something that could be quite tricky in SQL can be done in one procedure in SAS.  No point programming another system from within SAS.

One simple example, SQL does not have logical observation order, so lagging data would mean sub-querys or joins, but in SAS you can just lag to previous value.  Obs can be identifed just by _n_ as another example, which SQL can't do.