BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ravikumar_RT
Fluorite | Level 6

Hello ,

Hope you all doing good.

I need a help on weekly dates for my monthly reporting

I need to identify week start date and end date and thats for every Monday to Sunday pattern,

also its for rolling back 6 months to current reporting month

example:-

if report run is 1st November 2017, I need weekly start date and end date of every monday to sunday from 1st of April 2017 to 30th September 2017(6 months)

I have a historical SAS dataset with ID, Intake_date and Amount columns and i need to report Intake date wise weekly sales for rolling six month on a monthly basis.

Please suggest me with a sas code (it will be more helpful if any sas code automation suggestions).

Thanks,
Ravi

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

Hello,

 

data want;
format startDate endDate date date9. day $1.;

keep date day;

reportDate='1Nov2017'd;

endDate=intnx("day",reportDate,-1);
startDate=intnx("month",reportDate,-6);

date=startDate;

do while (date<=endDate and i<400);
    i+1;

	day=" ";
	if weekday(date)=1 then day="M";
	if weekday(date)=7 then day="S";

	date=intnx("day",date,1);

	if day ne " " then output;
end;
run;

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

To cumulate values per week, create a normalized date variable by using the intnx function:

weekdate = intnx('week',intake_date,0,'b');

You can then use proc summary with by weekdate to get weekly values.

A similar calculation can be done for months.

Or you can calculate the weekdate that was 6 months ago and start your report from that.

gamotte
Rhodochrosite | Level 12

Hello,

 

data want;
format startDate endDate date date9. day $1.;

keep date day;

reportDate='1Nov2017'd;

endDate=intnx("day",reportDate,-1);
startDate=intnx("month",reportDate,-6);

date=startDate;

do while (date<=endDate and i<400);
    i+1;

	day=" ";
	if weekday(date)=1 then day="M";
	if weekday(date)=7 then day="S";

	date=intnx("day",date,1);

	if day ne " " then output;
end;
run;
Ravikumar_RT
Fluorite | Level 6
Hi,
Thanks for your reply.

Above code is giving results from sunday to next monday i.e 01OCT2017(Sunday) to 02OCT2017(Monday) however i need sunday to previous monday exmple:- 25SEP2017 to 01OCT2017.

Please assist me.

Regards,
Ravi
gamotte
Rhodochrosite | Level 12
I'm not sure i understand what you're asking for. Can you provide an example for the desired output ?
Ravikumar_RT
Fluorite | Level 6
Hi,

I have a main input SAS dataset something like this

Master_ID INTAKE_DATE Division No_Of_Stores Sales_amt
1 01-Feb-2017 Central 10 50
1 02-Feb-2017 Central 20 90
2 03-Apr-2017 Southern 40 65
2 17-Apr-2017 Southern 15 70
3 10-Apr-2017 Southern 17 50
3 08-May-2017 Northern 19 90
5 01-Jul-2017 Northern 29 65
7 17-Sep-2017 Eastern 39 70
7 30-Sep-2017 Eastern 99 70

and I need all transactions which happend every starting of monday till end of sunday weekly basis in a month.
I want for rolling 6 months(from 1st april 2017 till 30th sep 2017). the output should be like this

Monday Sunday Division Total_Stores Total_Sales_amt
03-Apr-2017 09-Apr-2017 Central 2 500
10-Apr-2017 16-Apr-2017 Southern 9 900
17-Apr-2017 23-Apr-2017 Northern 10 65
24-Apr-2017 30-Apr-2017 Eastern 15 70


Thanks,
Ravi
Ravikumar_RT
Fluorite | Level 6

Hi All,

 

Can any one in this community assist me with my below mentioed request please.

 

Thanks,

Ravi

Ravikumar_RT
Fluorite | Level 6

Hi,

 

I have added just one small logic and it worked fine...:) here is the code:

 

data want(rename = (date = Monday));
format startDate endDate date date9. day $1.;

keep date Sunday;

reportDate="&month_run."d;

endDate=intnx("day",reportDate,-1);
startDate=intnx("month",reportDate,-6);

date=startDate;

do while (date<=endDate and i<400);
    i+1;

    day=" ";
    if weekday(date)=1 then day="M";
    date=intnx("day",date,1);
    if day ne " " then output;
    Sunday = date+7;
end;
Format Sunday Date9.;
run;

 

 

Thanks all for your inputs and helping.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1535 views
  • 3 likes
  • 3 in conversation