topic Re: Counting antibiotics by a defined period eg. by month. in SAS Enterprise Guide

Counting antibiotics by a defined period eg. by month.

Sharan — Fri, 26 Aug 2022 03:01:00 GMT

Hello fellow SAS users,

I have a data set with the columns: Child_ID which is linked to their mother_ID. Sometimes a mother_ID can occur twice because it’s for the second child. The mother (mother_ID) is linked to their antibiotics prescription called antibiotics_ID and dispensing_date and pregnancy_start_date.

Now I have created nine monthly dates till birth e.g. Month1, month2 month3…month9. Month1 is from pregnancy_start_date to the end of Month1.

Assume a mother may be prescribed 0, 1, or multiple antibiotics per month.

I need to create a count of Num_of_antibiotics each month.

Child_ID Mother_ID Pregnancy_start_date Antibiotics_ID dispensing_date Month1 Month2 Month3 … Count_month1 count_month2 count_month3

Re: Counting antibiotics by a defined period eg. by month.

PaigeMiller — Fri, 26 Aug 2022 10:30:39 GMT

Show us a portion of the data you have. This MUST be shown as SAS data step code, which you can type in yourself, or which you can create by these instructions.

You will be much better off doing this in a long format rather than a wide format. Each month is an observation rather than each month is a column.

Re: Counting antibiotics by a defined period eg. by month.

ballardw — Fri, 26 Aug 2022 16:55:49 GMT

How are you defining "month"? If you have Pregnancy_start_date and Dispensing_date as actual SAS date values then you can calculate the month number using those. One variable with the number of months in a long data set that looks like

Mother_id antibiotics_ID Dispensing_date Pregnancy_start_date Month, one record for each prescription/dispensing_date where "month" is the month number: 1,2,...,9 (10? depending on your "month" assignment rules)

Will allow you to make a report that looks like using code similar to

proc tabulate data=need;
   class mother_id pregnancy_start_date month;
   table mother_Id*pregnancy_start_date,
         month*n
   ;
run;

The result would one row for each mother by pregnancy_start, columns for each month number and the count of records for that month.

BTW, you probably do not want to consider Child_id because of multiple birth pregnancies. Involve those and a single prescription could be counted for each child. If you data has multiple child_id for a single prescription then you need to filter the data a bit.