Hi SAS @USERS I am hoping someone can help me solve this. I have been looking up into adding a report where data will show rolling for 4 weeks: (current_week, prev_week, prev2_week and prev3_week). knowing that week will start from Friday-Thursday. Also while setting up these weeks rolling, I need to make sure that it should start from Nov-October for each year). Last week Last week 2 Weeks ago 2 Weeks ago 3Weeks ago 3Weeks ago 4Weeks ago 4Weeks ago A B A B A B A B ************I have this macro that can also be added and help******************
%macro beginyr();
%if %eval(&LastSaturday)=%eval(&FiscalStartSat) %then %do;
data _null_;
call symput("dt_st",&FiscalStart);
run;
%end;
%else %do;
**Otherwise it will set it to the Friday of previous week;
data _null_;
call symput("dt_st",intnx('day', &run_date, (-1)*(weekday(&run_date))-8));
run;
%end;
%mend;
%beginyr;
%macro endyr();
%if %eval(&LastSaturday)=%eval(&FiscalEndSat) %then %do;
**set to November 1 if first run of the fiscal year;
data _null_;
call symput("dt_ed",&FiscalEnd);
run;
%end;
%else %do;
**Else Friday of previous week (begin of reporting);
data _null_;
call symput("dt_ed",intnx('day', &run_date, (-1)*%sysfunc(weekday (&run_date))-2));
run;
%end;
%mend;
%endyr;
%let dt_ed_prev = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-9 )); *Thursday of previous week;
%let dt_ed_prev2 = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-16 )); *Thursday of previous week ;
%let dt_ed_prev3 = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-23 )); *Thursday of previous week ;
%let dt_ed_prev4 = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-30 )); *Thursday of previous week;
%let dt_st_prev = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-15 )); *Friday two weeks ago;
%let dt_st_prev2 = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-22 )); *Friday two weeks ago;
%let dt_st_prev3 = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-29 )); *Friday two weeks ago;
%let dt_st_prev4 = %sysfunc(intnx(day, &run_date, (-1)*%sysfunc(weekday (&run_date))-36 )); *Friday two weeks ago; I previously used this code for monthly rolling: data monthly_base; set rollup_base_main; if month(&dt_ed.)>=3 then do; current_mth=month(&dt_ed.); prev_mth=(month(&dt_ed.)-1); prev2_mth=(month(&dt_ed.)-2); end; if month(&dt_ed.)=1 then do; current_mth=1; prev_mth=12; prev2_mth=11; end; if month(&dt_ed.)=2 then do; current_mth=2; prev_mth=1; prev2_mth=12; Thanks in advance
... View more
