Create cummulative overview in 1 step in stead of several steps / procs...?

[Wouter]

Hi,

I've got a question again! Should be simple (I think...), but not for me...

I've got a dataset with dates (so including full daynumbers) beginning at 2008, including amounts and intems. What I want is a cummulative overview per month of the data (so at the end of all periods a total of sold items including a total of the bucks / EUR / $). Little example:

Date (DMY) Amount Number
01-01-2008 10 5
01-01-2008 15 7
02-02-2008 9 4
05-02-2008 15 6
01-01-2009 20 10
01-01-2009 1 1
05-06-2009 3 2
04-06-2010 20 8
01-01-2010 25 12

And I want my output something like:

01-01-2008 25 13
02-02-2008 24 10
01-01-2009 21 11
05-06-2009 23 10
01-01-2010 25 12


When I try something like this, I have to use at least 2 datasteps: to gether the data, to format the data, to create extra variables, and to create a proc report including an "out" statement.

If it is possible (what I think, do loops?), I do want to have this process in 1 datastep. Does anyone have any idea? Thanks for your input in advance!!

Data test1;
set X;
format sldo_ultmo_per_bkng comma12.;
datum = former_datetime/86400;
format datum comma12.;
_year=year(datum); _month=month(datum);
counter=1; * --> to compute the number of items in the proc report below...;
run;

proc report data=test1 out=test2;
Columns _year _month sldo_ultmo_per_bkng counter;
define Jaar / group width=20 order=internal;
define Maand/ group width=20 order=internal;
define sldo_ultmo_per_bkng / sum;
define counter / n;
run;

[sbb]

Look at using the INTNX function and "MONTHS" for argument #1. Also, you can consider using a SAS output format to display year/month components only. Your code can accumulate based on the "PERIOD" derived variable:

FORMAT PERIOD YYMMS7. ;
PERIOD = INTNX('MONTH',,0);

Also, PROC SUMMARY can do the summarization for you, to include an internal sort using the CLASS statement, as well generate your _FREQ_ variable, which could feed your report step, as required. And, it's possible that PROC REPORT with GROUP can do something similar.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Hi,

Please try below code and let me know...


proc sql;
create table test1 as
select b.date as date1 format=ddmmyy10., sum(amt) as amt, sum(nbr) as nbr
from test a left join (select min(date) as date format=mmddyy10. from test group by month(date), year(date)) b
on month(a.date)=month(b.date) and year(a.date)=year(b.date)
group by b.date;
quit;


Please check once you required output that u have mentioned wrong as per your input.
It might be a typo...

As required monthly totals in one step , use above code.

Regards,
Uday Duddu

[Ksharp]

As Udaydudu said.


[pre]
Date (DMY) Amount Number
01-01-2008 10 5
01-01-2008 15 7
02-02-2008 9 4
05-02-2008 15 6
01-01-2009 20 10
01-01-2009 1 1
05-06-2009 3 2
04-06-2010 20 8
01-01-2010 25 12




01-01-2008 25 13
02-02-2008 24 10
01-01-2009 21 11
05-06-2009 23 10
01-01-2010 25 12
[/pre]


Is it right?
And whether do you want only one data step ? or maybe consider use proc sql which has more flexibility.
And the dataset is already sorted by date?


Ksharp Message was edited by: Ksharp

[Ksharp]

Ok.anyway.
If you do not mind output looks like this:
[pre]
amount_ number_
date Sum Sum

JAN2008 25 12
FEB2008 24 10
JAN2009 21 11
JUN2009 23 10
JAN2010 25 12
[/pre]





[pre]
data sum;
input date : ddmmyy10. amount number;
datalines;
01-01-2008 10 5
01-01-2008 15 7
02-02-2008 9 4
05-02-2008 15 6
01-01-2009 20 10
01-01-2009 1 1
05-06-2009 3 2
04-06-2009 20 8
01-01-2010 25 12
run;

proc means data=sum nway;
class date;
format date monyy7.;
var amount number;
output out=result(drop= _type_ _freq_) sum= /autoname;
run;
proc print noobs;run;
[/pre]



Ksharp

[deleted_user]

Hello,

Assuming your data set is sorted one solution to do it in a single data step follows:

data out;
set in end=z;

retain prev_m prev_y new_date;

act_m=month(date);
act_y=year(date);

if act_m=prev_m and act_y=prev_y then do;
tot_am+amount;
tot_nb+number;
end;

else do ;
ac=_N_-1;
if ac gt 0 then do;
set in(drop=_all_) point=ac;
output;
end;
tot_am=amount;
tot_nb=number;
new_date=date;

end;

prev_m=month(date);
prev_y=year(date);

if z then output;

keep new_date tot_am tot_nb;

format new_date date7.;

run;

Marius