DATA Step, Macro, Functions and more

Finding the sum over mtd interval

Reply
Senior User
Posts: 1

Finding the sum over mtd interval

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.

Super User
Posts: 19,770

Re: Finding the sum over mtd interval

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;
Super User
Posts: 11,343

Re: Finding the sum over mtd interval

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;
Ask a Question
Discussion stats
  • 2 replies
  • 123 views
  • 1 like
  • 3 in conversation