BookmarkSubscribeRSS Feed
xzromon
Calcite | Level 5

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.

2 REPLIES 2
Reeza
Super User

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
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 889 views
  • 1 like
  • 3 in conversation