BookmarkSubscribeRSS Feed
Sharan
Obsidian | Level 7

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.

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Sharan
Obsidian | Level 7

 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
Diamond | Level 26

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;
--
Paige Miller
Sharan
Obsidian | Level 7

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
Diamond | Level 26

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.

--
Paige Miller
Sharan
Obsidian | Level 7

 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
Diamond | Level 26

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.

--
Paige Miller

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

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
  • 7 replies
  • 2380 views
  • 2 likes
  • 2 in conversation