DATA Step, Macro, Functions and more

SUm By Dates

Reply
Regular Contributor
Posts: 229

SUm By Dates

i have

data report;
input dat tran;
format dat mmddyy8.;
cards;
10/12/98 45
10/16/98 66
10/26/98 23
11/06/00 89
11/20/00 75
11/23/00 45
11/29/00 63

Now i want the sum of tran between 10/12/98 to 10/16/98, and i want the sum of tran from 11/06/00 to 11/23/00 in to a new variable Tot.if the trans is not between the mentioned dates it should remain same .


output shd be

dat tran Total
10/12/98 45 -
10/16/98 66 101
10/26/98 23 23
11/06/00 89 -
11/20/00 75 -
11/23/00 45 203
11/29/00 63 63

Message was edited by: Main

Message was edited by: Main Message was edited by: Main
Frequent Contributor
Posts: 95

Re: SUm By Dates

My editing didn't work. I'll try reposting Message was edited by: 1162
Frequent Contributor
Posts: 95

Re: SUm By Dates

Well, this isn't very elegant, but it produces the output you're looking for. In order to run this, you have to hard code each date range (and increment the Period by 1) in a table called ranges. After that, I think it's pretty automatic.

[pre]data report;
input dat mmddyy8. tran;
format dat mmddyy8.;
cards;
10/12/98 45
10/16/98 66
10/26/98 23
11/06/00 89
11/20/00 75
11/23/00 45
11/29/00 63
;
run;

data ranges;
set report;
if '12Oct1998'd le dat le '16Oct1998'd then do; Period=1; output; end;
if '06Nov2000'd le dat le '23Nov2000'd then do; Period=2; output; end;
run;

proc sql;
create table sums as
select period, dat, max(dat) as End, case when dat=max(dat) then sum(tran) else . end as Total
from ranges
group by period
order by period, dat;
quit;

data report (drop=period end);
merge report (in=a) sums (in=b);
by dat;
if a and not b then total=tran;
run;[/pre]
Ask a Question
Discussion stats
  • 2 replies
  • 156 views
  • 0 likes
  • 2 in conversation