数据集指定日期缺失时，如何用SAS代码获取前一个非节假日数据

Let's fix this problem so your code automatically falls back to the most recent non-holiday date with data when your target date is a holiday (or has no records at all). Here are two practical, easy-to-implement approaches:

Approach 1: Handle It Directly in Teradata (Most Efficient) #

This method uses a subquery to find the latest date on or before your target date that has data in dw.act. If your target date is a holiday with no records, it'll grab the previous valid date in one single database query—super efficient and clean.

Updated SAS Code: #

%let date = '2016-12-31'; /* Wrap date in single quotes for Teradata date literal */
proc sql; 
  connect to teradata as tera (user=testuser password=testpass); 
  create table new as 
  select * from connection to tera (
    select acct, org 
    from dw.act 
    where date = (
      /* Get the max date <= target date that has existing records */
      select max(date) 
      from dw.act 
      where date <= date &date. /* Use Teradata's date() function to cast the literal correctly */
    )
  ); 
  disconnect from tera; 
quit;

If You Have a Dedicated Holidays Table: #

If your company maintains a separate holidays table (e.g., dw.holidays with a holiday_date column), you can explicitly exclude holidays from the search to ensure you only pick workdays:

%let date = '2016-12-31';
proc sql; 
  connect to teradata as tera (user=testuser password=testpass); 
  create table new as 
  select * from connection to tera (
    select acct, org 
    from dw.act 
    where date = (
      select max(a.date) 
      from dw.act a
      /* Left join to filter out holidays */
      left join dw.holidays h on a.date = h.holiday_date
      where a.date <= date &date. 
        and h.holiday_date is null /* Ensure the date isn't a holiday */
    )
  ); 
  disconnect from tera; 
quit;

Approach 2: Iterative Check in SAS (More Flexible) #

If you need extra control (like logging each date check or adding custom rules), you can use a SAS macro loop to test dates starting from your target and moving backward until you find one with data.

SAS Macro Code: #

%let target_date = '2016-12-31';
%let current_date = &target_date.;
%let has_data = 0;

/* Loop until we find a date with existing records */
%do %until(&has_data. = 1);
  proc sql noprint;
    connect to teradata as tera (user=testuser password=testpass);
    /* Check if current date has any records */
    select count(*) into :has_data from connection to tera (
      select 1 from dw.act where date = date &current_date.
    );
    disconnect from tera;
  quit;

  /* If no data, move back one day */
  %if &has_data. = 0 %then %do;
    /* Convert SAS date to Teradata-friendly string format */
    %let current_date = "%sysfunc(intnx(day, %sysfunc(inputn(%sysfunc(compress(&current_date.,"'")), yymmdd10.)), -1), yymmdd10.)";
  %end;
%end;

/* Fetch data for the valid date we found */
proc sql;
  connect to teradata as tera (user=testuser password=testpass);
  create table new as
  select acct, org from dw.act where date = date &current_date.;
  disconnect from tera;
quit;

Quick Tips: #

• Date Formatting: Always wrap your date literal in single quotes and use Teradata's date() function to avoid type mismatch errors.
• Performance: Approach 1 is ideal for most scenarios because it minimizes database calls. Use Approach 2 only if you need to add custom logic (like skipping weekends in addition to holidays).

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