categorize dates with gaps.

[AbletMuhtar]

Hello All, 

I have a dataset containing dates with gaps, and my objective is to detect these gaps between the dates and subcategorize them based on the continuity of each gap. Thanks.

This is what i have:

Cat1	Cat2	AdmDt
A	1	01/01/2022
A	1	01/02/2022
A	1	01/03/2022
A	1	01/10/2022
A	1	01/11/2022
A	1	01/12/2022
A	2	01/01/2022
A	2	01/02/2022

This is what i want:

Cat1	Cat2	AdmDt	Subcategory_id
A	1	01/01/2022	1
A	1	01/02/2022	1
A	1	01/03/2022	1
A	1	01/10/2022	2
A	1	01/11/2022	2
A	1	01/12/2022	2
A	2	01/01/2022	3
A	2	01/02/2022	3

[Reeza · Accepted solution]

Use DIF() and check if it's 1 and otherwise, reset the counter. 

This assumes your dates are sorted and in order.

data want;
set have;
by cat1 cat2 admdt;

retain subcategory_id;

date_dif =dif(admdt);

if first.cat1 then subcategory_id=1;
else if first.cat2 or date_dif ne 1 then subcategory_id + 1;

*drop date_dif;
run;

[AbletMuhtar]

it worked, thank you 😀

[john_mccall]

Hi.  I am not sure if this is what you are looking for, but consider the following:

/* Create sample table*/
Data Example;
infile datalines dlm=',';
Input Cat1:$1. Cat2 :$1. AdmDt :mmddyy10.;
format admdt mmddyy10.;
datalines;
A,1,01/01/2022
A,1,01/02/2022
A,1,01/03/2022
A,1,01/10/2022
A,1,01/11/2022
A,1,01/12/2022
A,2,01/01/2022
A,2,01/02/2022
A,2,01/07/2022
;
run;
/*Sort table*/
proc sort data=example;
by Cat1 Cat2 AdmDt;
/* Use first., last. and lag to check values and assign a value to SubCat*/
Data Categorize;
set example;
by Cat1 Cat2 AdmDt;
Retain SubCat 0;
if first.cat2 then SubCat+1;
If not first.cat2 and admDt >sum(lag(Admdt)+1) then SubCat+1;
run;

[AbletMuhtar]

Thank you, it worked.😀