DATA Step, Macro, Functions and more

Mapping dummy variables to calendar dates for days covered by medication

Reply
New Contributor
Posts: 4

Mapping dummy variables to calendar dates for days covered by medication


Hello,

I have a large administrative pharmacy dataset and I'm interested in examining days of medication coverage. I used the article below (attached--see "compliance measure by proportion days covered" starting on page 3) to create dummy variables for each day in a 500 day period indicating if that day was covered by medication or not.  I'd like to map those dummy variables day1-day500 to actual calendar days based on fill dates, because I am interested in whether patients were covered by medications during certain periods (ie. the month of August 2014).  Because this is not study data, day 1 does not correspond to the same date for everyone in my sample. Is there a way to do this?

Thank you for your help!

Best,

Corey

Message was edited by: Corey P Thanks to everyone who helped! I found a work around by setting everyone's start date to the same value (rather than using start date=filldt1 as in the original code developed from the paper). 

Super User
Posts: 11,343

Re: Mapping dummy variables to calendar dates for days covered by medication

Do you have a variable that does correspond to the first date for each patient? If so, is it a SAS date value?

New Contributor
Posts: 4

Re: Mapping dummy variables to calendar dates for days covered by medication

Hi!

Thanks for helping with this.  Yes, I have a variable for the first date for each patient, called start_dt.  It is a SAS date value.

-C

Super User
Posts: 11,343

Re: Mapping dummy variables to calendar dates for days covered by medication

Something like this would get you the dates would go something like this:

I am assuming you really want the dates where the dummy value was coded as 1 to get the dates medication was actually administered.

data want;

     set have;

     array dummy d1-d500; /* what every you named the dummies*/

     array dates date1-date500;

    format date1-date500 mmddyy10.;

     do _i_ = 1 to dim(dummy); /* we could hard code the limit but you might change to a different number of dummies and then we needn't change this line*/

     if d[_i_] = 1 then dates[_i_] = intnx('day',start_dt,_i_-1);

     end;

run;

New Contributor
Posts: 4

Re: Mapping dummy variables to calendar dates for days covered by medication

Hi ballardw,

This is close.  What I'm trying to get is instead of generic dummy variables called day1-day500, i want 1/0 variables (where 1=medication , 0=no medication) and the NAME of the variable is the date.  I don't want the value of the variable to be a date.  So for example, i would check the variable JULY31 and it would have values of 1/0 for each patient, to indicate whether the patient had a medication on that day.  So i realize this type of coding scheme will result in more than the original day1-day500 variables (because the date range is more than 500, when looking across all patients), but that's okay.

Thanks for giving it a try!

-C

Super User
Posts: 19,817

Re: Mapping dummy variables to calendar dates for days covered by medication

Actually, expand your data so that you have a single record for each day the patient is on a drug and then transpose it.

Super User
Posts: 19,817

Re: Mapping dummy variables to calendar dates for days covered by medication

Post some sample data.

A solution would be of the form:

1. Find min/max dates

2. Set up array with those dates as boundary

3. For each patient loop through array and set to 1.

UNTESTED:

data want;

set have;

array trt_days(min_date:max_date) days1-days750 (0*750);

do i=1 to ndays;

trt_days(start_date-1+i)=1;

end;

run;

New Contributor
Posts: 4

Re: Mapping dummy variables to calendar dates for days covered by medication

Hi Reeza,

thank you for looking into this too.  I will attach practice data momentarily.

So for patient 1 with a start date (start_dt) of June 1, 2013, instead of the variable "day1", i want a variable to be renamed JUN012013, and still have have the values of 1.  This However, for patient 2 with a start+_dt of June 4, 2013, I want the variable day 1 to be renamed JUN042013 and take the value of 0.

Bes,t
C

Super User
Posts: 19,817

Re: Mapping dummy variables to calendar dates for days covered by medication

So for patient 1 with a start date (start_dt) of June 1, 2013, instead of the variable "day1", i want a variable to be renamed JUN012013, and still have have the values of 1.  This However, for patient 2 with a start+_dt of June 4, 2013, I want the variable day 1 to be renamed JUN042013 and take the value of 0.

I don't think this is correct as it isn't possible to rename the same variable multiple times for each patient.

I expect that you want the dates listed and then a 1/0 for the first date to the end date. Also, that date format won't allow the data to sort properly.

Keep in mind it may be easier to not transpose the data at all depending on what you want to do in the future, i.e. calculate number of days per patient or coverage by date.

data part1;

set have;

ndays=sum(of day1-day20);

do i=1 to ndays;

date=start_dt+i-1;

format date yymmdd8.;

output;

end;

run;

Transpose this :

proc transpose data=part1 out=want;

by patient;

id date;

run;

Super User
Posts: 11,343

Re: Mapping dummy variables to calendar dates for days covered by medication

At this point I would say it is time to ask "What are you going to do with this data?". It may be that you have a thought for analysis that restructuring the data might be a better than this many variables per record. I have found that almost every time analysis involves dates I do not want to work with some character date substitute and it may be that having data in the form of :

PatientID Date Medicated

1               1/1/2000     1

1               1/2/2000     0

1               1/3/2000     0

1               1/4/2000     0

1               1/5/2000     1

etc.

leads to easier analysis.

Ask a Question
Discussion stats
  • 9 replies
  • 789 views
  • 0 likes
  • 3 in conversation