To find zero interval continuity..
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;
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;
To find zero interval continuity.. 02
Thanks PGStats..
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, 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;
@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.
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;
Thank you Ksharp, for your time and efforts..
Thanks Ksharp..
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!
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 .
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!!
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
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.