BookmarkSubscribeRSS Feed
Corey
Calcite | Level 5


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). 

9 REPLIES 9
ballardw
Super User

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

Corey
Calcite | Level 5

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

ballardw
Super User

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;

Corey
Calcite | Level 5

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

Reeza
Super User

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

Reeza
Super User

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;

Corey
Calcite | Level 5

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

Reeza
Super User

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;

ballardw
Super User

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.

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!

How to Concatenate Values

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.

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