Finding the sum over mtd interval

[xzromon]

Hello all,

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:

Month      new_col

Jan2017    99

Feb2017   105

......

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.

[Reeza]

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;

[ballardw]

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;