Counting antibiotics by a defined period eg. by month.

[Sharan]

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

[PaigeMiller]

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.

[ballardw]

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.