topic SUm By Dates in SAS Programming

SUm By Dates

R_Win — Wed, 15 Apr 2009 06:06:45 GMT

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

Re: SUm By Dates

1162 — Wed, 15 Apr 2009 13:35:32 GMT

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

Re: SUm By Dates

1162 — Fri, 17 Apr 2009 21:13:02 GMT

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]