topic Re: categorize dates with gaps. in SAS Programming

categorize dates with gaps.

AbletMuhtar — Tue, 18 Jul 2023 19:04:45 GMT

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

Re: categorize dates with gaps.

Reeza — Tue, 18 Jul 2023 19:15:32 GMT

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;

Re: categorize dates with gaps.

john_mccall — Tue, 18 Jul 2023 19:43:03 GMT

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;

Re: categorize dates with gaps.

AbletMuhtar — Wed, 19 Jul 2023 14:36:30 GMT

it worked, thank you 😀

Re: categorize dates with gaps.

AbletMuhtar — Wed, 19 Jul 2023 14:37:42 GMT

Thank you, it worked.😀