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
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:
@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
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
@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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.