Prescription counts and numbers used per year.

[Sharan]

Dear SAS users,

I have a large dataset of children.  I am looking at children’s antibiotic consumption patterns from birth up to 5 years of age.

I have the following variables of children:  childs_ID,  date of birth, name of antibiotic ( eg. Antibiotic_A, Antibiotic_B,  Antibiotic_C),  date_antibiotic_dispensed. Some children have no antibiotic dispensed date which means they did not use any while many have several antibiotics.

From the birth date of the child, I have created the  following variables: Date_of_1st_birthday,

Date_of_2nd_birthdays, Date_of_3rd _birthdays, Date_of_4th _birthdays,

Date_of_5th_birthdays,

Now, I'd like to create several variables for each child as follows:

1. Total counts of antibiotics per child over 5 years period

2. Number of antibiotics used in the 1st Year ( i.e between birth to the first birthday

3. Number of antibiotics used in the 2nd Year (i.e., between first birthday and 2nd birthday)

4. Number of antibiotics used in the 3rd Year (i.e., between 2nd birthday and 3rd birthday)

PS: Please see attached example of dataset/ variables.

I am looking forward to your kind help.

[PaigeMiller]

Many of use will not download Microsoft Office attachments as they can be a security threat. So data in Excel is unacceptable.

You need to provide a portion of your data via working SAS data step code, which you can type in yourself, or use this macro. Please test the code first to make sure it works.

[Sharan]

 Here it is:

data have;
input id $ birthday :date9. prescriptiondate :date9. drug $;
format prescriptiondate date9.;
format birthday :date9.;
datalines;
1 02JAN2000 10APR2002 S
1 02JAN2000 15JUN2003 T
1 02JAN2000 08SEP2004 S
2 09APR2002 15JUN2003 S
2 09APR2002 15OCT2005 S
2 09APR2002 16DEC2005 T
2 09APR2002 15JUN2003 T
2 09APR2002 12DEC2005 S
3 04FEB2001 15JUN2003 T
3 04FEB2001 17NOV2005 S
3 04FEB2001 18FEB2006 S
;
RUN;

[PaigeMiller]

So, I'm not entirely sure exactly what output you want, but this should contain all that you have asked for (and maybe some stuff you have not asked for)

data have2;
    set have;
    years = intck('year',birthday,prescriptiondate,'c');
run;
proc summary data=have2;
    class id years drug;
    var prescriptiondate;
    types id id*drug id*years id*drug*years;
    output out=want n=ndrugs;
run;

[Sharan]

Hi friends, id like my output  dataset to be as follows:

child_id	birth_date	prescription_date	date_1st_BD	date_2nd_BD	date_3rd_BD	antibiotic_type	systemic_counts_in_year_1	systemic_counts_in_year_2	systemic_counts_in_year_3
1	1-Jan-00	10-Feb-00	1-Jan-01	1-Jan-02	1-Jan-03	S	1	0	0
1	1-Jan-00	20-Apr-00	1-Jan-00	1-Jan-02	1-Jan-03	S	2	0	0
1	1-Jan-00	20-Jun-00	1-Jan-01	1-Jan-02	1-Jan-03	S	3	0	0
1	1-Jan-00	20-Jun-00	1-Jan-01	1-Jan-02	1-Jan-03	T	3	0	0
2	1-Feb-00	11-Feb-00	1-Feb-01	1-Feb-02	1-Feb-03	T	0	0	0
2	1-Feb-00	10-Apr-00	1-Feb-01	1-Feb-02	1-Feb-03	S	1	0	0
2	1-Feb-00	12-Jul-01	1-Feb-01	1-Feb-02	1-Feb-03	S	1	1	0
3	3-Mar-00	13-Jul-01	3-Mar-01	3-Mar-02	3-Mar-03	T	0	0	0
3	3-Mar-00	14-Oct-00	3-Mar-01	3-Mar-02	3-Mar-03	S	1	0	0
3	3-Mar-00	11-Apr-01	3-Mar-01	3-Mar-02	3-Mar-03	S	1	1	0
4	4-Apr-00	10-Oct-02	4-Apr-01	4-Apr-02	4-Apr-03	S	0	0	1
4	4-Apr-00	11-Oct-02	4-Apr-01	4-Apr-02	4-Apr-03	S	0	0	2

[PaigeMiller]

You have changed the data since your earlier post, and now the output data set does not correspond to the input data set. Please don't do that.

I would like to see the results you want using your original data.

[Sharan]

 Hi  there, 

Yes, i have changed the data. I am sorry about that. The reason for changing was that   so one could understand  it better. I also wanted the hypothetical data to show children also received the S- drug in second year of life  ( i.e between first birthday and second birthday )  which is not the case with original data.

Anyway, here i am pasting the output the id like to see  from the original dataset.

child_id	birthday	prescriptiondate	Drug	first_bd	second_bd	third_bd	S-drug in year1	S-drug in year2	S-drug in year2
1	02-Jan-00	10-Apr-00	S	02-Jan-01	02-Jan-02	02-Jan-03	1	0	0
1	02-Jan-00	15-Jun-00	T	02-Jan-01	02-Jan-02	02-Jan-03	1	0	0
1	02-Jan-00	08-Sep-00	S	02-Jan-01	02-Jan-02	02-Jan-03	2	0	0
2	09-Apr-02	15-Jun-02	S	09-Apr-03	09-Apr-04	09-Apr-05	1	0	0
2	09-Apr-02	15-Oct-05	S	09-Apr-03	09-Apr-04	09-Apr-05	0	0	0
2	09-Apr-02	16-Dec-05	T	09-Apr-03	09-Apr-04	09-Apr-05	0	0	0
2	09-Apr-02	15-Jun-03	T	09-Apr-03	09-Apr-04	09-Apr-05	0	0	0
2	09-Apr-02	12-Dec-05	S	09-Apr-03	09-Apr-04	09-Apr-05	0	0	0
3	04-Feb-01	15-Jun-01	T	04-Feb-02	04-Feb-03	04-Feb-04	0	0	0
3	04-Feb-01	17-Nov-05	S	04-Feb-02	04-Feb-03	04-Feb-04	0	0	0
3	04-Feb-01	18-Feb-06	S	04-Feb-02	04-Feb-03	04-Feb-04	0	0	0

[PaigeMiller]

The original data had prescription date 10APR2002 in row 1. I will be happy to work with that if only I knew what you wanted as output. The output you just showed has prescription date 10APR2000 in row 1, which is not correct for the data I am working with. There are many other rows where prescription data has changed. I can't get correct output if the data has changed. So I need to see the desired output for the data you posted originally, not for any other data.