BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Costasg
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
DF
Fluorite | Level 6 DF
Fluorite | Level 6

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;

View solution in original post

5 REPLIES 5
art297
Opal | Level 21

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
Fluorite | Level 6 DF
Fluorite | Level 6

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
Calcite | Level 5

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
Fluorite | Level 6 DF
Fluorite | Level 6

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
Calcite | Level 5

Yeap, got the mistake

Thank you all

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4967 views
  • 4 likes
  • 3 in conversation