如何在SAS PROC SQL直通式查询中按字符串格式日期过滤提取记录？

Great question—this is a common gotcha when using SAS pass-through with Oracle, since the code inside the CONNECTION TO block runs directly on Oracle, not SAS. You can absolutely parse the string dates on Oracle's side to filter efficiently—you just need to use Oracle-native functions instead of SAS functions. Here's how to do it:

Step 1: Use Oracle's TO_DATE() to parse your string column #

Your rtp_date column is stored as a string in DD/MM/YYYY HH:MM:SS format. Oracle's TO_DATE() function can convert this to a proper date/time value using the format mask 'DD/MM/YYYY HH24:MI:SS' (note: Oracle uses MI for minutes, not MM—that's reserved for months!).

Step 2: Adjust your SAS macro variables for Oracle #

Your &start_date. and &end_date. macros are likely SAS date values (numeric, counting days since 1960-01-01). You'll need to convert them to a string format Oracle understands (like YYYY-MM-DD) before passing them into the pass-through query.

Full Working Code #

First, generate formatted string versions of your date macros:

/* Convert SAS date values to Oracle-compatible date strings */
%let start_date_str = %sysfunc(put(&start_date., yymmddd10.));
%let end_date_str = %sysfunc(put(&end_date., yymmddd10.));

/* If you need to include time (e.g., end at 23:59:59 instead of midnight): */
/* %let end_date_str = %sysfunc(put(&end_date., yymmddd10.)) || ' 23:59:59'; */

Then update your pass-through query to use Oracle functions:

PROC SQL; 
CONNECT TO ORACLE AS xxxxx (AUTHDOMAIN="xxxxx" PATH=xxxxx preserve_comments); 
CREATE TABLE work.new_data AS 
SELECT * FROM CONNECTION TO xxxxx (
  SELECT /*+parallel(16)*/ var1, var2, var3 
  FROM oracle_data 
  /* Parse string to date, then filter */
  WHERE TO_DATE(rtp_date, 'DD/MM/YYYY HH24:MI:SS') 
        BETWEEN TO_DATE('&start_date_str.', 'YYYY-MM-DD') 
        AND TO_DATE('&end_date_str.', 'YYYY-MM-DD')
  /* Optional: Skip rows with invalid date strings to avoid errors */
  -- AND VALIDATE_CONVERSION(rtp_date AS DATE, 'DD/MM/YYYY HH24:MI:SS') = 1
); 
DISCONNECT FROM xxxxx; 
QUIT;

Key Notes for Efficiency #

• Filtering happens in Oracle: This avoids pulling the entire dataset to SAS first—just like your original efficient pass-through query. Only rows matching the date range are transferred.
• Handle invalid dates: Uncomment the VALIDATE_CONVERSION line if your rtp_date column has malformed strings. This ensures only valid dates are processed, preventing query errors.
• Optional: Add a function index: If you run this filter often, ask your Oracle DBA to create a function-based index on the parsed date. This will speed up the query even more:

  CREATE INDEX idx_oracle_data_rtp_date ON oracle_data (TO_DATE(rtp_date, 'DD/MM/YYYY HH24:MI:SS'));
  

This approach keeps the performance benefits of pass-through while solving the string date parsing issue. No more full data pulls or inefficient post-processing in SAS!

内容的提问来源于stack exchange，提问作者Wolff