Hello all,
My data (excel file) is consisted of daily volume prices on stocks. What I want to do is convert them in monthly by adding their daily volume.
So my data now looks like:
DATE STOCK1 STOCK2 STOCK3 ........
1/3/1996 100 80 70
2/3/1996 95 82 76
3/3/1996 90 75 81
.
.
.
.
31/5/2000.................................................
And I want to make it like this:
DATE STOCK1 STOCK2 STOCK3 ........
31/3/1996 500 450 300
30/4/1996 200 500 450
.
.
31/12/2000 490 750 781
So, I want to have the sums of each month in a new file. Any suggestions?
Many thanks in advance
Maybe also something using proc means (etc.) would work:
proc means noprint chartype nway missing data=stock;
class date;
var stock1-stock3;
format date monyy6.;
output out=stock2 (drop=_type_ _freq_) sum=;
run;
If you need the date listed to always be the last day of the month then this would need another step. In that a minor change to Art's suggestion might be easier:
proc sql;
create table stock3 as
select
intnx('month',date,0,'end') as date format ddmmyys8.,
sum(stock1) as stock1,
sum(stock2) as stock2,
sum(stock3) as stock3
from
stock
group by
calculated date;
quit;
proc sql would be one way of doing it. You could use something like:
proc sql noprint;
create table want as
select distinct year(date) as year,
month(date) as month,
sum(stock1) as stock1,
sum(stock2) as stock2,
sum(stock3) as stock3
from have
group by calculated year, calculated month
;
quit;
Maybe also something using proc means (etc.) would work:
proc means noprint chartype nway missing data=stock;
class date;
var stock1-stock3;
format date monyy6.;
output out=stock2 (drop=_type_ _freq_) sum=;
run;
If you need the date listed to always be the last day of the month then this would need another step. In that a minor change to Art's suggestion might be easier:
proc sql;
create table stock3 as
select
intnx('month',date,0,'end') as date format ddmmyys8.,
sum(stock1) as stock1,
sum(stock2) as stock2,
sum(stock3) as stock3
from
stock
group by
calculated date;
quit;
Guys I tried all the proposed sollutions but I am facing problems,
In the first case with sql I get the following prob:
52 proc sql;
53
54 create table stock3 as
55
56 select
57
58 intnx('month',date,0,'end') as date format ddmmyys8.,
59
60 sum(a1) as a1,
61
62 sum(a2) as a2,
63
64 sum(a3) as a3
65
66 from
67
68 vol
69
70 group by
71
72 calculated date;
ERROR: The SUM summary function requires a numeric argument.
73
74 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Whereas using the proc means I get:
proc means noprint chartype nway missing data=vol;
26 class date;
27 var a1-a98;
ERROR: Variable a1 in list does not match type prescribed for this list.
28 format date monyy6.;
29 output out=vol2 (drop=_type__freq_)sum=;
30 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.VOL2 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Any suggestions?
I am attaching a sample file
It sounds like you've imported fields A1-A3 as text, rather than numbers. Can you double check your import routine, or the info for the table Vol?
Yeap, got the mistake
Thank you all
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.