How to create a report that can generate 4 weeks rolling

[Nala]

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

[ballardw]

Before diving into the weeds of macro coding it might help to know whether you have access to the SAS/ETS products.

If you are not sure then you can run this code to see if they are licensed:

proc setinit;
run;

You log will show something similar to :

Current version: 9.04.01M7P080520
Site name:    'MY ORGANIZATION NAME HERE'.
Site number:  XXXXXXXXX.
CPU A: Model name='' model number='' serial=''.
Expiration:   30JAN2060.
Grace Period:  45 days (ending 15MAR2060).
Warning Period: 45 days (ending 29APR2060).
System birthday:   18MAY2017.
Operating System:   WX64_WKS.
Product expiration dates:
---Base SAS Software
      30JAN2060 (CPU A)
---SAS/STAT
      30JAN2060 (CPU A)
---SAS/GRAPH
      30JAN2060 (CPU A)
---SAS/Secure 168-bit
      30JAN2060 (CPU A)
---SAS/Secure Windows
      30JAN2060 (CPU A)
---SAS Enterprise Guide
      30JAN2060 (CPU A)
---SAS/ACCESS Interface to PC Files
      30MAY2062 (CPU A)

If you see SAS/ETS then there a number of procedures intended to deal with things like rolling time intervals and likely can skip most of this macro coding.

[Nala]

No, I don't have access to the SAS/ETS products

[ballardw]

Okay. No ETS so the hard way.

What part do you need help with? I don't see much in the way of "report" or even actual data.

Some descriptions of what is needed would help.

Your macro code in the first post has many macro variables used that you did not provide any example values for so are hard to tell what they should look like or what the expected output might be.

Examples: &LastSaturday &FiscalStartSat  &FiscalStart &run_date as starting values.