topic How do I include dataset options with a reference to a view created in PROC SQL without an error? in SAS Programming

How do I include dataset options with a reference to a view created in PROC SQL without an error?

cbsmith_aus — Mon, 01 Mar 2021 22:27:45 GMT

I get an error when referencing an SQL view with dataset options in and SQL query;

27 proc sql;
28 create table test2 as
29 select *
30 from test_vw (where = (valid_date
SYMBOLGEN: Macro variable ASATDATE resolves to 22339
30 ! = &asatdate.))
31 ;
ERROR: Unable to process SAS data set options for SQL view WORK.TEST_VW.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32 quit;
NOTE: The SAS System stopped processing this step because of errors.

If I create the view in a data step I do not get an error however it is 10 times slower as it can not exploit indexes. Also if I reference the view with dataset options in a data step there is no error. However I was planning on replacing many very large SAS tables with SAS views without changing numerous SAS programs in production. Many programs currently reference these tables in SQL queries with dataset options.

Does any one know how to resolve this issue in the SAS view? Resolving the issue in the SAS programs using the views is not an option as that would require changes to numerous programs.

Re: How do I include dataset options with a reference to a view created in PROC SQL without an error

SASKiwi — Mon, 01 Mar 2021 22:40:41 GMT

Why not use the WHERE clause directly?

proc sql;
  create table test2 as
  select *
  from test_vw
 where valid_date = &asatdate.
;
quit;

Re: How do I include dataset options with a reference to a view created in PROC SQL without an error

ChrisNZ — Tue, 02 Mar 2021 01:43:18 GMT

As far as I know, that's the limitations you have to live with:

SQL views can use indexes but not DS options.

DS views can do the opposite.

I have no idea why, and that's clearly an odd set of limitations for both types of views.

I'd raise it with Tech Support just in the hope they can improve this in the future, but don't hold your breath.

Re: How do I include dataset options with a reference to a view created in PROC SQL without an error

cbsmith_aus — Tue, 02 Mar 2021 07:56:21 GMT

As I mentioned previously, I am replacing very large SAS tables with SAS views to reduce unsustainable space usage. These table are referenced with dataset options in hundreds of SAS programs across a large financial services institution. If I use SQL views (which are faster than data step views) these programs will fail with this error. Changing hundreds of production and ad-hoc SAS programs is not an option.

I need a solution with the SAS SQL view or I will have to use Data step views which can be much slower as they do not exploit indexes. I have also asked SAS Tech Support. Still waiting for a response.