Using SUBSTR function in SAS 9.4 with SAS Studio

[dolldata]

Hello,

We are running into an interesting problem...we are currently switching from SAS 9.2 (server) / 9.3 (desktop) to running SAS 9.4 on a virtual server and using SAS Studio.  [The upgrade has been a long time coming!  :-)]

With the "old" SAS, the following query works perfectly:

rsubmit;
proc print data = db.customers (obs = 10);
where substr(postal_code,1,5) = '04107';
var postal_code state;
run;
endrsubmit;

LOG:

NOTE: There were 10 observations read from the data set DB.CUSTOMERS.
WHERE SUBSTR(postal_code, 1, 5)='04107';
NOTE: The PROCEDURE PRINT printed page 1.
NOTE: PROCEDURE PRINT used (Total process time):
real time 3.38 seconds
cpu time 0.06 seconds

OUTPUT:

Obs        POSTAL_CODE   STATE

75          041072704              ME
8284      041072615              ME
15263    041079682              ME
23753    041079603              ME
25667    041071623              ME
29707    041072615              ME
34185    041071219              ME
45532    041070257              ME
58933    041071156              ME
68820    041071423              ME

However, in SAS Studio, on SAS 9.4, the following query yields zero observations, but we don't see any errors or problems in the notes:

proc print data = db.customers (obs = 10);
where substr(postal_code,1,5) = '04107';
var postal_code state;
run;

LOG:

NOTE: No observations were selected from data set DB.CUSTOMERS.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.06 seconds

cpu time 0.00 seconds

If we take the SUBSTR out and pull a specific postal code (all 9 characters), the query works:

proc print data = db.customers (obs = 10);
where postal_code = '041072704';
var postal_code state;
run;

LOG:

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.20 seconds

cpu time 0.09 seconds

OUTPUT:

Obs POSTAL_CODE STATE 1 2 3

041072704	ME
041072704	ME
041072704	ME

The SUBSTR funciton works correctly within a PROC SQL statement. 

It's rare that we would use the SUBSTR function like this (in a PROC PRINT), but the issue has us worried that something has changed between the versions that could cause us to run into other issues, too.

Anyone have ideas on what we might be missing?

THANK YOU!

[Reeza]

This is likely a SAS tech support question. 

One thing though - was the data imported in both or are you accessing a server or stored data somewhere?

If it's imported moving from Windows to a Unix server that could be part of the issue. 

[dolldata]

---

@Reeza wrote:

This is likely a SAS tech support question. 

One thing though - was the data imported in both or are you accessing a server or stored data somewhere?

If it's imported moving from Windows to a Unix server that could be part of the issue. 

---

Hi Reeza,

Both attempts were accessing the same database on a server.

I'll submit a ticket to tech support as well.  Thank you!

[Reeza]

The next check would the ODBC driver is my guess.

Good Luck!

[dolldata · Accepted solution]

In case anyone else runs into a similar issue, SAS Technical Support was able to give us a solution!

A modified explanation from them: "Starting in 9.4M2 when you are accessing Oracle 12c or later and your code includes (obs= ), a "fetch first n rows" is appended to the query that is submitted to the database.  This results in Oracle only providing the [first n observations] to SAS which explains why the first [n] rows worked but [then] could no longer find the match.  Another piece to this issue is...using a function that is not passed to the database automatically.  [Running] with [the SQL_FUNCTIONS= option in your LIBNAME statement], the WHERE was passed to the database so the obs= didn't affect the pulling of results."

So, we have added the "sql_functions = all" to our database libname statement, and that works.  Support also provided us these helpful links:

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0k1cax0gzsdxdn1a3...

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0f64yzzxbsg8un1uw...