Convert Daily volume to monthly

[Costasg]

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

[art297]

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;

[DF · Accepted solution]

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;

[Costasg]

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

[DF]

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?

[Costasg]

Yeap, got the mistake

Thank you all