- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
I am trying to figure what is the bast way to determine the members that have a continuous enollement from 01/25/2017 for 18 months. I used the lag function and was able to calculate their start of the coverage and the term of the coverage.
The orginal table contains this data:
Patid Drug_Cov Drug_term
1 01Jan2014 31Dec2014
1 01Jan2015 31Dec2015
1 01Jan2016 31Dec2016
1 01Jan2017 01Jan3000
2 01Jan2015 31Dec2014
2 01Jan2016 31Dec2015
2 01Jan201 01Jan3000
3 01Feb2015 11May2015
3 12May2015 31Oct2015
I used the lag function to determine the continuous the drug coverage and drug term date:
With the lag function, this is the table:
Patid Drug_Cov Drug_Term
1 01Jan2014 01Jan3000
2 01Jan2015 01Jan3000
3 01Feb2015 31Oct2015
How do I now determine the members that has an 18 months continuous enrollent from today date?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What do your mean by "18 months continuous enrollent from today date"? Currently active with a starting data at least 18 months ago? That's what I assume. The following program should do (I had to correct two of your data entries?):
data have;
input Patid Drug_Cov :date9. Drug_term :date9. ;
format drug_cov drug_term $date9.;
datalines;
1 01Jan2014 31Dec2014
1 01Jan2015 31Dec2015
1 01Jan2016 31Dec2016
1 01Jan2017 01Jan3000
2 01Jan2015 31Dec2014
2 01Jan2015 31Dec2015 /* change 01jan2016 to 01jan2015*/
2 01Jan2016 01Jan3000 /* change 01jan201 to 01jan2016*/
3 01Feb2015 11May2015
3 12May2015 31Oct2015
run;
data spells (drop=next_cov first_cov);
set have (keep=patid);
by patid ;
retain first_cov;
merge have
have (firstobs=2 keep=drug_cov rename=(drug_cov=next_cov));
/* if this is beginning-of-spell, set first_cov value*/
if first.patid or drug_cov-1 > lag(drug_term) then first_cov=drug_cov;
/* See if at end-of-spell and whether its date range qualifies*/
if last.patid or next_cov > drug_term + 1 then do;
drug_cov=first_cov;
if drug_term>=today() and intck('month',drug_cov,today()+1,'continuous')>=18 then output;
end;
run;
The "continuous" argument of the INTCK function says not to count calendar month boudaries, but rather use the actual date of drug_cov vs today as a way to count months.
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What do your mean by "18 months continuous enrollent from today date"? Currently active with a starting data at least 18 months ago? That's what I assume. The following program should do (I had to correct two of your data entries?):
data have;
input Patid Drug_Cov :date9. Drug_term :date9. ;
format drug_cov drug_term $date9.;
datalines;
1 01Jan2014 31Dec2014
1 01Jan2015 31Dec2015
1 01Jan2016 31Dec2016
1 01Jan2017 01Jan3000
2 01Jan2015 31Dec2014
2 01Jan2015 31Dec2015 /* change 01jan2016 to 01jan2015*/
2 01Jan2016 01Jan3000 /* change 01jan201 to 01jan2016*/
3 01Feb2015 11May2015
3 12May2015 31Oct2015
run;
data spells (drop=next_cov first_cov);
set have (keep=patid);
by patid ;
retain first_cov;
merge have
have (firstobs=2 keep=drug_cov rename=(drug_cov=next_cov));
/* if this is beginning-of-spell, set first_cov value*/
if first.patid or drug_cov-1 > lag(drug_term) then first_cov=drug_cov;
/* See if at end-of-spell and whether its date range qualifies*/
if last.patid or next_cov > drug_term + 1 then do;
drug_cov=first_cov;
if drug_term>=today() and intck('month',drug_cov,today()+1,'continuous')>=18 then output;
end;
run;
The "continuous" argument of the INTCK function says not to count calendar month boudaries, but rather use the actual date of drug_cov vs today as a way to count months.
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much! It worked!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi there, how did you create your output dataset using lag and retain function to get continous enrollment ?