Explicit SQL pass through in SAS Viya 4 on AWS

[thesasuser]

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.

[SASKiwi · Accepted solution]

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;

[Tom]

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?