Proc sql calculate previous saturday or end of last week

Question

Is there a better way of doing this?

asof = CAST(DATEADD(week, DATEDIFF(week,'19000101',CURRENT_TIMESTAMP),
       '1899-12-30T19:00:00') AS DATE)

Basically, I am trying to use previous week's Saturday as my filter on Proc Sql. No matter what day I run this query it should give me the date(not timestamp) of last Saturday

Thank you!

Please show you're using it in a filter. In your example it's creating a variable and how you're filtering may affect the logic. — Reeza, Oct 25 '18 at 20:07

score 2 · Answer 1 · answered Oct 25 '18 at 16:19

2

%let last_saturday = intnx ('week.7', today()-1, 0);
%let last_saturday = %sysfunc(intnx (week.7, %sysfunc(today())-1, 0), yymmdd10.);
%put NOTE: &last_saturday;

%let last_saturday_sq = %sysfunc(quote(&last_saturday,%str(%')));
%put NOTE: &last_saturday_sq;

proc sql;
  … connection to … 
     (cast &last_saturday_sq as DATE) as asof
  …

answered Oct 25 '18 at 16:19

Richard

25,390
3
25
38

Unfortunately, this did not work. I am not sure why as it doesn't give me any error message. – JCP Oct 25 '18 at 17:26
try the same code without a macro variable, instead use a hard-coded value and see if the same 'did not work' happens. If so, it's not the macro. If things do work, it's somewhere in the macro/pass-through area. – Richard Oct 25 '18 at 19:40

Proc sql calculate previous saturday or end of last week

1 Answers1