topic Explicit SQL pass through in SAS Viya 4 on AWS in SAS Viya

Explicit SQL pass through in SAS Viya 4 on AWS

thesasuser — Wed, 24 Jul 2024 01:46:03 GMT

Hello
We are having SAS Viya 4 (Release 2023.10) running on AWS.
I have executed the following code in SAS Studio. I see that a table is created but no data is pulled.

79   
80   proc sql;
81   connect to ODBC as AWS(dsn='smDSN' user='username' password=xxx );
82   create table test as
83   select *
84     from connection to AWS(
85   select *
86         from dbTest.TableA
87        where myVar = '1-Jan-24')
88   ;
NOTE: Table WORK.TEST created, with 0 rows and 254 columns.
89   disconnect from AWS;
90   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           2.50 seconds
      cpu time            0.07 seconds

Running the same code on SAS 9.4 pulls more than a million rows.

NOTE: Table WORK.TEST created, with 1731330 rows and 254 columns.

Wondering if there is a different way of doing this in Viya 4 ?
Requesting guidance from the community.

Re: Explicit SQL pass through in SAS Viya 4 on AWS

SASKiwi — Wed, 24 Jul 2024 02:40:39 GMT

I suggest you check the contents of myVar like this:

proc sql;
connect to ODBC as AWS(dsn='smDSN' user='username' password=xxx );
create table test as
select *
from connection to AWS(
select  myVar
       ,count(*) as Row_Count
from dbTest.TableA
group by myVar
);
disconnect from AWS;
quit;

Re: Explicit SQL pass through in SAS Viya 4 on AWS

Tom — Wed, 24 Jul 2024 02:47:34 GMT

where myVar = '1-Jan-24'

That is a very very strange string to search for. Is that supposed to be a DATE? Are you sure MYVAR is a character variable? Are you sure the variable doesn't actually have '01-JAN-2024' or '1-JAN-24' or any of the dozens of other ways to represent the first day of the year 2024 as a character string?