SAS Procedures

Help using Base SAS procedures
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2411 views
  • 0 likes
  • 3 in conversation