topic Convert Daily volume to monthly in SAS Procedures

Convert Daily volume to monthly

Costasg — Fri, 05 Aug 2011 00:50:39 GMT

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

Convert Daily volume to monthly

art297 — Fri, 05 Aug 2011 02:37:28 GMT

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;

Re: Convert Daily volume to monthly

DF — Fri, 05 Aug 2011 08:57:39 GMT

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;

Re: Convert Daily volume to monthly

Costasg — Fri, 05 Aug 2011 11:45:53 GMT

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;

54 create table stock3 as

56 select

58 intnx('month',date,0,'end') as date format ddmmyys8.,

60 sum(a1) as a1,

62 sum(a2) as a2,

64 sum(a3) as a3

66 from

68 vol

70 group by

72 calculated date;

ERROR: The SUM summary function requires a numeric argument.

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

Convert Daily volume to monthly

DF — Fri, 05 Aug 2011 11:52:01 GMT

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?

Convert Daily volume to monthly

Costasg — Fri, 05 Aug 2011 12:38:04 GMT

Yeap, got the mistake

Thank you all