topic Re: Using SUBSTR function in SAS 9.4 with SAS Studio in SAS Programming

Using SUBSTR function in SAS 9.4 with SAS Studio

dolldata — Mon, 28 Aug 2017 16:06:24 GMT

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!

Re: Using SUBSTR function in SAS 9.4 with SAS Studio

Reeza — Mon, 28 Aug 2017 16:10:12 GMT

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.

Re: Using SUBSTR function in SAS 9.4 with SAS Studio

dolldata — Mon, 28 Aug 2017 16:19:33 GMT

@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!

Re: Using SUBSTR function in SAS 9.4 with SAS Studio

Reeza — Mon, 28 Aug 2017 16:30:05 GMT

The next check would the ODBC driver is my guess.

Good Luck!

Re: Using SUBSTR function in SAS 9.4 with SAS Studio

dolldata — Wed, 06 Sep 2017 22:17:22 GMT

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#n0k1cax0gzsdxdn1a3466l1gwtk9.htm

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