topic Re: How to sum rows of the same date for another variable with two levels (two drugs) in SAS Programming

How to sum rows of the same date for another variable with two levels (two drugs)

mmaximos — Thu, 07 Jul 2022 10:23:16 GMT

Hello all,

This is my first post in the SAS forum, hopefully I provide enough information but happy to adapt my question as needed. I am also relatively new to SAS, so my question may be simple.

I have a dataset for which I want to sum all rows of the same date into 1 row but separated by another variable which is drug (there are two drugs). I then want to sum all the total number of prescriptions dispensed by each of these two drugs for each day (this is a third column in my dataset).

How would I approach this?

Thank you

Re: How to sum rows of the same date for another variable with two levels (two drugs)

AlexBennasar — Thu, 07 Jul 2022 10:30:26 GMT

An example dataset would be nice, it's difficult to situate yourself without seeing it

Re: How to sum rows of the same date for another variable with two levels (two drugs)

PaigeMiller — Thu, 07 Jul 2022 10:32:33 GMT

@mmaximos wrote:

Hello all,

This is my first post in the SAS forum, hopefully I provide enough information but happy to adapt my question as needed. I am also relatively new to SAS, so my question may be simple.

I have a dataset for which I want to sum all rows of the same date into 1 row but separated by another variable which is drug (there are two drugs). I then want to sum all the total number of prescriptions dispensed by each of these two drugs for each day (this is a third column in my dataset).

How would I approach this?

Thank you

I agree with @AlexBennasar that providing a sample data set, and providing the desired output from that sample data set, would be greatly helpful. Nevertheless, this seems like something PROC SUMMARY will do easily.

Re: How to sum rows of the same date for another variable with two levels (two drugs)

mmaximos — Thu, 07 Jul 2022 10:58:32 GMT

Good point, I'm uncertain if I can share an actual sample of my dataset, therefore I've created this sample dataset in google sheets that mimics my dataset: https://docs.google.com/spreadsheets/d/15sXZBoHmgRVmBreG9JaPZWVTJ7npGpC5bQm3tGF04fc/edit?usp=sharing . The molecule column contains two drugs, the new prescriptions column contains the number of new prescriptions dispensed, the time period represents the year and month for that drug and new prescriptions dispensed. What I want to be able to do is total all the number of incidents of drug_1 and drug_2 for each day in the time period column and also sum all the total number of new prescriptions for each day by drug. The time period column contains multiple years, so if I try to sum by month then I lose the ability to separate by year.

Thank you for your help, it's very much appreciated.

Re: How to sum rows of the same date for another variable with two levels (two drugs)

PaigeMiller — Thu, 07 Jul 2022 11:17:37 GMT

Some of us (many of us?) will not work with data from spreadsheets, as they can be a security risk and are often unhelpful in providing data in meaningful form for use in SAS code. Please provide (a portion of) the data as working SAS data step code which you can type in yourself (and please test your code to ensure that it works) or which you can create following these instructions.

Re: How to sum rows of the same date for another variable with two levels (two drugs)

AlexBennasar — Thu, 07 Jul 2022 11:23:35 GMT

Number of incidents by day (in the _freq_ var of the o1 dataset):

proc summary data=yourDS;
class molecule time_period;
types molecule*time_period;
output out=o1;
run;

New prescriptions by day and drug:

proc summary data=yourDS;
var prescriptions;
class molecule time_period;
types molecule*time_period;
output out=o2 sum=;
run;

In fact, the second summary is enough, as you have there the _freq_ var too...

Re: How to sum rows of the same date for another variable with two levels (two drugs)

Kurt_Bremser — Thu, 07 Jul 2022 14:08:34 GMT

Your link is not a SAS dataset, it's an Excel spreadsheet. Your column names are not valid SAS names.

Please post usable data in this form:

data have;
input Molecule $ Total_New_Prescriptions Time_Period $6.;
datalines;
Drug_1 4 201611
Drug_2 1 201611
Drug_2 9 201611
Drug_1 2 201612
Drug_1 4 201612
Drug_1 3 201612
Drug_2 9 201613
Drug_1 2 201613
Drug_2 6 201613
Drug_2 1 201613
;

What does your time period represent? It can't be months, as there ain't a month 13. Are these Julian dates?

Re: How to sum rows of the same date for another variable with two levels (two drugs)

mmaximos — Sat, 09 Jul 2022 13:10:52 GMT

Thanks for the questions. You are correct, this is not a SAS dataset, which was explained in my initial reply as I cannot link my dataset and I am still learning how to navigate SAS. Thank you for the example of how to post a dataset sample. You are also correct, there is no month 13, that was a flaw in my entry. I think a fellow SAS user may have posted a potential solution, which I will try. If I am still stuck, I will make sure to post a dataset that can be navigated in SAS format if I need to ask further questions.

Re: How to sum rows of the same date for another variable with two levels (two drugs)

mmaximos — Sat, 09 Jul 2022 14:39:05 GMT

This worked great, thank you kindly!

Re: How to sum rows of the same date for another variable with two levels (two drugs)

mmaximos — Sat, 09 Jul 2022 14:41:12 GMT

Thank you, I think my question has been answered, but this is helpful information to know for the future. Appreciated.