select data load date by previous date

[sarahzhou]

Hi,

I have defined an macro prev_day

%let prev_day = sysfunc(intnx(day, %sysfunc(today(),-1), date9.)

I have a column name data_loade_dt, which is datetime 

I wish to select the data loaded before previous date,

so in my where condition I write:

where datepart(date_load_dt) < '& prev_day'd

Which result a wrong format SAS cannot compare the pre_day in my selection.

Please advise.

Thank you!

[Patrick · Accepted solution]

Assuming your real code was already of better quality than what you've posted the reason is likely that you've enclosed the macro variable in single quotes. You need double quotes for the macro variable to resolve.

Below working sample code with all the syntax you've posted fixed.

%let prev_day = %sysfunc(intnx(day, %sysfunc(today()),-1), date9.);
%put &=prev_day;

data have;
  format date_load_dttm datetime21.;
  do i=-3 to 3;
    date_load_dttm=intnx('dtday',datetime(),i); 
    output;
  end;
run;

data want;
  set have;
  if datepart(date_load_dttm) < "&prev_day"d;
run;

[sarahzhou]

@Patrick , thank you! It worked!