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.
Why not use the WHERE clause directly?
proc sql;
create table test2 as
select *
from test_vw
where valid_date = &asatdate.
;
quit;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.