topic Re: Counting swipes within two weeks in SAS Programming

Counting swipes within two weeks

GregorClegane — Mon, 06 Jan 2020 11:56:42 GMT

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!

Re: Counting swipes within two weeks

ed_sas_member — Mon, 06 Jan 2020 12:30:33 GMT

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?

Re: Counting swipes within two weeks

GregorClegane — Mon, 06 Jan 2020 12:36:57 GMT

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

Re: Counting swipes within two weeks

GregorClegane — Mon, 06 Jan 2020 13:22:51 GMT

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

Re: Counting swipes within two weeks

ed_sas_member — Mon, 06 Jan 2020 13:35:07 GMT

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.?)

Re: Counting swipes within two weeks

GregorClegane — Mon, 06 Jan 2020 13:36:57 GMT

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.

Re: Counting swipes within two weeks

ed_sas_member — Mon, 06 Jan 2020 14:05:12 GMT

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;

Re: Counting swipes within two weeks

GregorClegane — Mon, 06 Jan 2020 14:32:39 GMT

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!

Re: Counting swipes within two weeks

ed_sas_member — Mon, 06 Jan 2020 14:33:31 GMT

Thank you @GregorClegane !

Have a great day