Counting swipes within two weeks

[GregorClegane]

Good Morning!

I have been struggling with the best way to approach this issue and I thought I would come on here and post.  I have a table with Employee ID along with check in dates.  I am trying to determine how to use SAS to first break up my dates into 2 week (14 day) chunks and then count the number of swipes (check in days within that 14 day period).  I was thinking about using min/max, but I think I would lose days if I did that.  

So I am thinking my data will ultimately look like this:

Employee       Date Range                       Swipes

Member001   01JAN2019 - 14JAN2019  16

Member001   05MAR2019 - 19MAR2019 2

Member002   02JAN2019 - 15JAN2019  2

Just not sure how to format that into proper SAS coding.

Thank you for any guidance!

[ed_sas_member]

Hi @GregorClegane 

Happy new Year and welcome to the community!

Could you please share a sample of your input data so that we can help you?

[GregorClegane]

Thank you for the welcome and Happy New Year! @ed_sas_member 

Here is a sample of the data that I have.  Just two columns so nothing huge.

Member_ID               Swipe_Date

2115                           12FEB2019

2115                           13FEB2019

2115                           14FEB2019

2115                           17FEB2019

2115                           19FEB2019

5192                           20JUN2019

5192                           30JUN2019

5192                           20JUN2019

9528                           04JAN2019

9528                            21JAN2019

9528                            24JAN2019

[GregorClegane]

Hello @ed_sas_member and Happy New Year.  Thank you for the welcome

Here is the sample data that I have.  Only two columns so not too bad.

Member_ID      Date_Swiped

5299                 04JAN2019

5299                 05JAN2019

5299                 11JAN2019

5299                 14JAN2019

6023                  05JUL2019

6023                  02AUG2019

6023                  20DEC2019

7288                  01FEB2019

9588                 31AUG2019

9588                  02SEP2019

9855                  04SEP2019

[ed_sas_member]

Hi @GregorClegane 

Thank you for the sample data.

Could you please specify also the desired output?

What is the rule to identify the 14 days ? (e.g. the first day = the first check in date, the beginning of the month, etc.?)

[GregorClegane]

Hey @ed_sas_member  the first day of the swipe would start the countdown for the first 14 days and encompass any dates from day 1 - day 14.  Then any other days after that would start the process of counting 14 over again.

[ed_sas_member · Accepted solution]

Hi @GregorClegane 

Here is an attempt to answer your question. Let me know!

data have;
	input Member_ID Date_Swiped:date9.;
	format Date_Swiped date9.;
	datalines;
5299 04JAN2019
5299 05JAN2019
5299 11JAN2019
5299 14JAN2019
6023 05JUL2019
6023 02AUG2019
6023 20DEC2019
7288 01FEB2019
9588 31AUG2019
9588 02SEP2019
9855 04SEP2019
;
run;

proc sql noprint;
	select max(count) into:max_obs from (select count(Member_ID) as count from have group by Member_ID);
quit;

data ref;	
	set have;
	by Member_ID;
	format Date_Swiped_initial Start_range End_range date9.;
	if first.Member_ID then Date_Swiped_initial = Date_Swiped;
	retain Date_Swiped_initial;
	do i = 1 to &max_obs. by 2;
		Start_range = intnx('week',Date_Swiped_initial,i,"s")-7;
		End_range = Start_range + 13;
		output;
	end;
	drop i; 
run;

proc sql;
	create table want as
	select distinct a.member_id, a.Date_Swiped, b.Start_range, b.End_range
	from have as a inner join ref as b
	on b.Start_range <= a.Date_Swiped <= b.End_range and a.member_id = b.member_id ;
quit;

[GregorClegane]

Thank you @ed_sas_member it looks to work perfectly.  I am able to count the data based on the date ranges now.  I really appreciate the help!

[ed_sas_member]

Thank you @GregorClegane !

Have a great day