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:
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:
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!
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:
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.
@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!
The next check would the ODBC driver is my guess.
Good Luck!
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:
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.