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:
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!!
format sldo_ultmo_per_bkng comma12.;
datum = former_datetime/86400;
format datum comma12.;
counter=1; * --> to compute the number of items in the proc report below...;
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;
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.
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;
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.