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 |
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;
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;
it worked, thank you 😀
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;
Thank you, it worked.😀
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.