topic Re: Summing values in a column dependent on dates and conditions in other columns in SAS Programming

Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 00:09:13 GMT

Hi,

I am new to SAS, and think in Excel terms. I have three columns: (1) a reference; (2) a score; and (3) a date. The task I am trying to perform is equivalent to a sumifs function in excel.

I want to sum the total score of all entries in the score column for each reference, whose dates fall within defined ranges. I intend to create columns for each month so that I know what the total scores are for each reference for each month.

Ie I have list of thousands of scores with a reference and a date, and I want to know the total score of all entries for each reference that falls within each particular month.

Help needed!

Thanks in advance all.

Best,

Barney

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 00:45:08 GMT

All you likely need is proc means.

The date ranges is your biggest issue, if you're using calendar months it becomes a trivial exercise, for going from 15 to the 14th of a month it's a bit more complicated but still doable.

Here's a quick tutorial on PROC MEANS:

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

And an example of using a format with a date within PROC MEANS.

https://gist.github.com/statgeek/0cae5568752959b035516d6ac07a20fb

This would be easier if you could provide sample data.

Instructions on providing data is available here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/tac-p/436960

@BarneyC77 wrote:

Hi,

I am new to SAS, and think in Excel terms. I have three columns: (1) a reference; (2) a score; and (3) a date. The task I am trying to perform is equivalent to a sumifs function in excel.

I want to sum the total score of all entries in the score column for each reference, whose dates fall within defined ranges. I intend to create columns for each month so that I know what the total scores are for each reference for each month.

Ie I have list of thousands of scores with a reference and a date, and I want to know the total score of all entries for each reference that falls within each particular month.

Help needed!

Thanks in advance all.

Best,

Barney

Re: Summing values in a column dependent on dates and conditions in other columns

PaigeMiller — Mon, 07 Jan 2019 01:45:05 GMT

For calendar months, it's pretty simple

proc summary data=have;
    class reference date;
    Var score;
    output out=want sum=sum_score;
    format date monyy.;
run;

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 10:25:58 GMT

Great - thank you. Is this through SAS Enterprise Guide? I am very new to SAS EG and even the tutorial seems to assume knowledge/experience I don't have. Sorry

Re: Summing values in a column dependent on dates and conditions in other columns

PaigeMiller — Mon, 07 Jan 2019 15:03:23 GMT

@BarneyC77 wrote:

Great - thank you. Is this through SAS Enterprise Guide? I am very new to SAS EG and even the tutorial seems to assume knowledge/experience I don't have. Sorry

Enterprise Guide can be used in two different ways. There is a point and click graphical user interface to generate analyses, and then you can also write and execute your own SAS code. Both the reply by @Reeza and myself are to be used as code you can execute in EG. I don't know how to accomplish what you want via the point-and-click graphical user interface.

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 15:53:11 GMT

You didn't mention EG so there was no way to know that. You've posted in the 'programming' forum, but there is an EG specific forum as well. If you're looking for an EG GUI solution, that would be in the Summary task.

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 16:07:02 GMT

Hi,
Thanks for your help with my SAS query. I'm happy with using either point and click or programming, whatever works. Are you able to help with some code?

Thanks,

Barney

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 16:17:34 GMT

If you want code that works with your data, you need to share your data.

Otherwise, the tutorials above should be sufficient. Just paste them in your editor, run and see how the output and input match up.

If you need a more step by step tutorial the following are some suggestions:

https://www.lexjansen.com/nesug/nesug08/ff/ff06.pdf

Video walkthrough of PROC MEANS - SAS Studio but may help

https://video.sas.com/detail/video/3306906230001/summary-statistics-using-sas-studio?autoStart=true&q=summary%20statistics