05-25-2017 08:44 PM
Here's my sample data, with date1 representing SAS date value in date9. format:
obs date1 amount
1 15Jan2017 25
2 17Jan2017 74
3 28Jan2017 10
4 01Feb2017 18
5 16Feb2017 37
6 20Feb2017 50
7 27Feb2017 44
....... and so on, with each coming month new records will be added.
I need to calculate sum of amount for month to date time interval.
For example, today being 26th day of a month:
if date1 is between the first day of a particular month and 26th, then do the sum
For Jan, if date1 between 01Jan2017 and 26Jan2017 then new_col=sum(amount), etc.
What would look like this at the end:
I'm looking for universal way to code this, in some sort of loop or macro.
Any suggestions would be most welcome.
Thanks in advance.
05-25-2017 09:15 PM
What happens if the date is 31st? You compare to February 28th?
Use rhe DAY() to get the day and filter out dates you don't want. Converting that to a macro is left as an exercise for you.
Proc means data = have; Where day(date) < = 26; Class date; Format date yymon7.; Var amount; Output out = want sum=mtd_total; Run;
05-26-2017 05:37 PM
data have; informat date1 date9.; format date1 date9.; input date1 amount; datalines; 15Jan2017 25 17Jan2017 74 28Jan2017 10 01Feb2017 18 16Feb2017 37 20Feb2017 50 27Feb2017 44 ; run; proc summary data=have nway; where day(date1) le 26; class date1 ; format date1 monyy7.; var amount; output out=want (drop=_:) sum=; run; proc print data=want; run; /* one way to make a report*/ proc tabulate data=have; where day(date1) le 26; class date1 ; format date1 monyy7.; var amount; table date1='Month', amount*sum=''; run;