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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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

--------------------------

View solution in original post

3 REPLIES 3
mkeintz
PROC Star

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

--------------------------
ssitharath0420
Quartz | Level 8

Thank you so much! It worked!

manya92
Fluorite | Level 6

Hi there, how did you create your output dataset using lag and retain function to get continous enrollment ?

 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 2020 views
  • 0 likes
  • 3 in conversation