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

To find zero interval continuity..

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

I interpreted your date interval requirement as continuous enrolment for the twelve months prior the index month, for the index month and for the eleven months after the index month.

 

data enrolment;
set sasforum.annual_cut;
array memday{12};
indexMonth = intnx("MONTH", index_dt, 0);
do i = 1 to 12;
    month = mdy(i, 1, year);
    days = memday{i};
    if -12 <= intck("MONTH", indexMonth, month) <= 11 then output;
    end;
format month indexMonth yymm.;
keep enrolId indexMonth month days;
run;

proc sql;
create table continuous as
select enrolId, indexMonth
from enrolment
group by enrolId, indexMonth
having sum(days) >= 2*365;
quit;
PG

View solution in original post

13 REPLIES 13
PGStats
Opal | Level 21

I interpreted your date interval requirement as continuous enrolment for the twelve months prior the index month, for the index month and for the eleven months after the index month.

 

data enrolment;
set sasforum.annual_cut;
array memday{12};
indexMonth = intnx("MONTH", index_dt, 0);
do i = 1 to 12;
    month = mdy(i, 1, year);
    days = memday{i};
    if -12 <= intck("MONTH", indexMonth, month) <= 11 then output;
    end;
format month indexMonth yymm.;
keep enrolId indexMonth month days;
run;

proc sql;
create table continuous as
select enrolId, indexMonth
from enrolment
group by enrolId, indexMonth
having sum(days) >= 2*365;
quit;
PG
Reema
Fluorite | Level 6

To find zero interval continuity.. 02

Camp
Fluorite | Level 6

Thanks PGStats..

PGStats
Opal | Level 21

It is simpler to change the approach slightly because there can be anywhere between 365+365+28 and 365+366+31 days in 25 consecutive months. The new approach is: 1) keep only complete months within the desired range, then 2) select ranges that include exactly 25 consecutive months.

 

data enrolment;
set sasforum.annual_cut;
array memday{12};
indexMonth = intnx("MONTH", index_dt, 0);
do i = 1 to 12;
    month = mdy(i, 1, year);
    days = memday{i};
    if -12 <= intck("MONTH", indexMonth, month) <= 12 and
        days = intck("DAY", month, intnx("MONTH", month, 1))then output;
    end;
format month indexMonth yymm.;
keep enrolId indexMonth month;
run;

proc sql;
create table continuous as
select enrolId, indexMonth
from enrolment
group by enrolId, indexMonth
having count(month) = 25;
quit;
PG
Ksharp
Super User
PG,
Why not use RANGE() in your original code?



proc sql;
create table continuous as 
select enrolId, indexMonth,  range(indexMonth) as range
from enrolment
group by enrolId, indexMonth
having sum(days) >= 2*365;
quit;

PGStats
Opal | Level 21

@Ksharp, I guess you mean RANGE(month)? I can't see how that would help.

 

See new version of the code for a simpler solution that can also accomodate any number of consecutive months. 

PG
Ksharp
Super User
OK. Test it on your own. I have no time to check it.



data key;
 set annual_cut;
 array x{*} memday1-memday12;
 do i=1 to dim(x);
  monyy=put(mdy(i,1,year),monyy7.);
  value=x{i};
  output;
 end;
 keep enrolid monyy value;
run;

data want;
 if _n_=1 then do;
  if 0 then set key;
  declare hash h(dataset:'key',hashexp:20);
  h.definekey('enrolid','monyy');
  h.definedata('value');
  h.definedone();
 end;
set annual_cut;
pre_sum=0;
 do i=-11 to 0;
  monyy=put(intnx('month',index_dt,i),monyy7.);
  if h.find()=0 then pre_sum+value;
 end;

post_sum=0;
 do j=0 to 11;
  monyy=put(intnx('month',index_dt,j),monyy7.);
  if h.find()=0 then post_sum+value;
 end;
 
if pre_sum ge 365 and post_sum ge 365 then output;

drop i j monyy value;
run;

Reema
Fluorite | Level 6

Thank you Ksharp, for your time and efforts..

Camp
Fluorite | Level 6

Thanks Ksharp..

Reema
Fluorite | Level 6

Hi Ksharp,

 

The codes that you have provided to me (which are aliter for below codes provided by PGStats) are giving distinct number of observations. Can you please look into it.

1. Even after changing the pre_sum do interval to: <do i =-12 to -1> and,

2. I am sure that there are no duplicates in final dataset that I get after running PGStats codes and your codes.

 

data enrolment;
set sasforum.annual_cut;
array memday{12};
indexMonth = intnx("MONTH", index_dt, 0);
do i = 1 to 12;
    month = mdy(i, 1, year);
    days = memday{i};
    if -12 <= intck("MONTH", indexMonth, month) <= 11 then output;
    end;
format month indexMonth yymm.;
keep enrolId indexMonth month days;
run;

proc sql;
create table continuous as
select enrolId, indexMonth
from enrolment
group by enrolId, indexMonth
having sum(days) >= 2*365;
quit;

 

Thanks!

Ksharp
Super User
That is because I counted
month=-11 to 0 
AND
month=0 to 11

Notice: I count month=0 twice.


PG only count month=0 once .


Reema
Fluorite | Level 6

Sorry Ksharp, but in last message I replied that I have changed the first do loop for pre_sum to -12 to -1 and I have checked that there are no duplicates in final datasets of yours i.e. "want" and final dataset of PG i.e. "continuous" and as I last remember yours' codes were giving less number of observations as compared to PG's.

 

Thanks!!

Ksharp
Super User
I am not following the message after that.
My code would not take care of duplicated value,since it is monthly (1-12) data.

if you want that change:
i =-11 to 0 
-->
i=-12 to -1


My code will give you the exact number as the number of original dataset.


If you have some data like this, mine is different with PG.
id   date
1   01jan2010
1   10feb2010
1   01jan2010
1   01jan2010

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 13 replies
  • 2634 views
  • 9 likes
  • 4 in conversation