how to convert dates into weekly increments

[msrenee1984]

Hello, I would like to know the best way to convert dates (about 13k rows) into weekly increments.  The sum of dates per week...  My dates range from Jan 1, 2025 to August 31, 2025. 

[antonbcristina]

HI @msrenee1984, please post your data using a DATA step as outlined here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat.... It would also be helpful to post an example of what you want the output dataset to look like. Are you looking for a sum of another variable by week, or a sum of observations by week?  

[msrenee1984]

Cristina, thank you so much for the reply.  Please see below:

PROC SQL;
CREATE TABLE r.Entries AS SELECT
entry_id,
entrydate,
entrycode FORMAT=$entrycode.
FROM data.base
WHERE entrydate BETWEEN '01JAN2025'D AND '31AUG2025'D AND entrycode IN ('01', '02', '11');
QUIT;

PROC SORT DATA=r.Entries NODUPS;
BY entry_id;
RUN;

I'd like my report to show how many entries occurred each week ex: Monday Jan 1st -Sunday Jan 5th, Monday Jan 6th-Sunday Jan12th, ...and so on or whatever increments are available for 7 day period or week long period.   Unsure what options are available...my requestor would like to know how many people entered each week.

[antonbcristina]

The code provided doesn't include data but maybe it's enough to go on. It seems like you've already removed duplicates and you're looking to count up the number of observations. A simple solution would be to use PROC FREQ to obtain counts, with a format applied to the date to display as week of the year.

proc freq data=have;
    tables entrydate;
    format entrydate weekw5.;
run;

The format applied to entrydate in the code above (WEEKWw.) will format the date variable to display a two digit year (25), followed by W, and the week of the year (example: Aug 31st, 2025 = 25W34), for a total of 5 characters in the displayed formatted value. PROC FREQ will automatically use the formatted value to aggregate results. 

Documentation for the format WEEKWw. available here: https://documentation.sas.com/doc/en/leforinforref/3.2/n1fg8h5vu3iuknn1jc178ar8e26r.htm (Note that this format assumes Monday is the start of the week, whereas WEEKUw. assumed Sunday is)

[msrenee1984]

Cristina thank you this works great; I will use this in conjunction with suggestion from user below to add the "wkstart" and "wkend" titles etc.!!

[PaigeMiller · Accepted solution]

I'd like my report to show how many entries occurred each week ex: Monday Jan 1st -Sunday Jan 5th, Monday Jan 6th-Sunday Jan12th, ...and so on or whatever increments are available for 7 day period or week long period. Unsure what options are available...my requestor would like to know how many people entered each week.

Important information! That means the solution from @antonbcristina will work, but you have the un-intuitive (to me) values of 25M34 to indicate the week. I have no idea when week 34 actually is. It also has the disadvantage of using 2 digits years. Using my solution above gives you weeks named by the Sunday of the week, such as 21SEP2025, which is much more intuitive to me, and then you also have a 4 digit year. After the code I provided, you can use PROC FREQ on the variable SUNDAY_OF_WEEK to do the counting.

[PaigeMiller]

Weeks are not uniquely defined, but lets assume you want your week (in this problem) to begin on Sunday.

You would convert each date to the start of the week (Sunday of that week), then you can do whatever arithmetic you want on the variable containing the start of the week. You can do this conversion using the INTNX function.

Example:

data fake;
    input date :date9.;
    cards;
22SEP2025
14AUG2025
;
data next;
    set fake;
    sunday_of_week=intnx('week',date,0,'b');
    format date sunday_of_week date9.;
run;

If , instead of weeks beginning on Sundays, you want weeks beginning on Monday, you would use

    monday_of_week=intnx('week.1',date,0,'b');

and so on.

[msrenee1984]

Thank you, Paige, this was very helpful, I got a little hung up at the "cards" step however I think I get the gist... much appreciated!

[PaigeMiller]

The data step with cards is to create a fake data set that I can use. We request that the person asking the question provide such a data set, but you didn't do that so I created my own data set to use.

[quickbluefish]

If you're looking for a *count* of dates per week, you can do something like this -- basically, find the start of the first and last weeks (here, using Sunday) in your data, use that to make a 'shell', then left join the data to the shell, grouping by week and getting a count of dates.

data dts;
length dt 4;
format dt date9.;
dt='07May2022'd;
do i=1 to 100;
	output;
	dt+rand('integer',1,25);
end;
run;

proc sql noprint; 
select intnx('week',min(dt),0,'b'), intnx('week',max(dt),0,'b')
into :dtstart trimmed, :dtend trimmed from dts;
quit;

data shell;
length wkstart wkend 4;
format wkstart wkend date9.;
do wkstart=&dtstart to &dtend by 7;
	wkend=wkstart+7;
	output;
end;
run;

proc sql;
create table weekly_counts as
select a.wkstart, a.wkend, count(b.dt) as ndts 
from
	shell A
	left join
	dts B
	on a.wkstart<=b.dt<a.wkend
group by a.wkstart, a.wkend
order by a.wkstart;
quit;

proc print data=weekly_counts; run; 

[msrenee1984]

thank you for the additional information, this worked very well, and my requestor appreciated the labeled weeks!