How to create date macro variable based on the last date WITH data

Question

I'm quite new to SAS and need your help regarding creating date macro variable.

I have 6 tables at the moment and all of them are refreshed on daily basis. Each month i'm trying to get the data with the last date stamp

%let last_month = %sysfunc(intnx(month,%sysfunc(today()),-1,e), yymmn6.);

and this only gives me the last date of previous month. This doesn't work for me as sometimes the last date of the record doesn't exist (for example if last record date of APril was 28th as 29,30 was a weekend).

Welcome to SAS. Are you asking for the last workday of previous month? Weekends should be removed, what about holidays? — whymath
– whymath, Commented Jul 27, 2023 at 3:01
What I'd prefer is to get the max date of previous month. Weekends/holidays data will be missed out hence it would be best to create a date macro to get the maximum date (that has data) of previous month — Scott
– Scott, Commented Jul 27, 2023 at 3:46

Reeza · Accepted Answer · 2023-07-27 15:08:22Z

1

If you're looking for with data, you can't use any functions but need to check a data set. If you can assume that as long as one data set has data for that date, the rest will, something like this should work.

proc sql noprint;
select put(max(date), yymmn6.) into :last_month from dataset1;
quit;

%put &last_month.;

Alternatively, if the last month is the last update date, you could see the last modified time for the dataset from the sashelp.vtable data as well.

answered Jul 27, 2023 at 15:08

Reeza

21.4k4 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

whymath · Accepted Answer · 2023-07-28 08:19:55Z

Look at this one: Exclude holidays and weekends.

data test;
  format date yymmdd10.;
  date=today();
  last_day_pre_month=intnx('month',date,-1,'e');

  array hol_nam[7]$16._temporary_('CHRISTMAS','EASTER','HALLOWEEN','MEMORIAL','NEWYEAR','THANKSGIVING','USINDEPENDENCE');
  array hol_dat[7]_temporary_;
  do i=1 to dim(hol_nam);
    hol_dat[i]=holiday(hol_nam[i],year(date));
  end;

  do until(result^=.);
    if last_day_pre_month not in hol_dat 
      and put(last_day_pre_month,weekday.) not in('1' '7') then result=last_day_pre_month;
    else last_day_pre_month=last_day_pre_month-1;
  end;
  put result=yymmdd10.;
run;

You may add more holidays as you wish.

However, I think Reeza's saying is right, you may need to get the date directly from an actual file, rather than an ideal condition.

Collectives™ on Stack Overflow

How to create date macro variable based on the last date WITH data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related