topic Re: select data load date by previous date in Advanced Programming

select data load date by previous date

sarahzhou — Tue, 18 Oct 2022 07:05:05 GMT

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!

Re: select data load date by previous date

Patrick — Tue, 18 Oct 2022 07:30:12 GMT

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;

Re: select data load date by previous date

sarahzhou — Tue, 18 Oct 2022 07:58:40 GMT

@Patrick , thank you! It worked!