BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
AbletMuhtar
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

4 REPLIES 4
Reeza
Super User

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
Fluorite | Level 6

it worked, thank you 😀

john_mccall
SAS Employee

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
Fluorite | Level 6

Thank you, it worked.😀

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 262 views
  • 2 likes
  • 3 in conversation