BookmarkSubscribeRSS Feed
Wouter
Obsidian | Level 7
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;
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Super User
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
Super User
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
Not applicable
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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 660 views
  • 0 likes
  • 4 in conversation