topic Re: Finding the sum over mtd interval in SAS Programming

Finding the sum over mtd interval

xzromon — Fri, 26 May 2017 00:44:53 GMT

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.

Re: Finding the sum over mtd interval

Reeza — Fri, 26 May 2017 01:15:09 GMT

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;

Re: Finding the sum over mtd interval

ballardw — Fri, 26 May 2017 21:37:03 GMT

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;