How can I combine/average values within this table?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

How can I combine/average values within this table?

Simple question but I can't seem to figure it out! I have a dataset on Microsoft Access and SAS of about a million option prices arranged with the following fields/columns:

DATE, COMPANY, PUT/CALL, PRICE

The PUT/CALL variable is an indicator variable that comes out as either PUT or CALL.

Example with numbers:

**DATE             COMPANY          PUT/CALL       PRICE**

2001/01/01       XOM                     PUT                10

2001/01/01       XOM                     CALL              12

2001/01/01       XOM                     PUT                11

2001/01/01       XOM                     CALL              13

What I need is for only uniqe combinations of DATE-COMPANY-PUT/CALL. Therefore for the same data, company, and put, I want to average the multiple values.

The output for my numerical example should be:

DATE             COMPANY          PUT/CALL       PRICE


2001/01/01       XOM                  PUT                10.5

2001/01/01       XOM                  CALL               12.5

The PRICE has now been averaged when we have the same DATE, COMPANY and PUT/CALL indicator.

Would someone know how I could use SAS, Microsoft Access or any other software to complete this?


Accepted Solutions
Solution
‎05-19-2013 04:28 PM
Respected Advisor
Posts: 4,919

Re: How can I combine/average values within this table?

You can use roughly the same query in Access or SAS/SQL :

in SAS

proc sql;

create table newTable as

select date, company, putCall, avg(price) as price

from oldTable

group by date, company, putCall;

quit;

in Access:

create table newTable as

select [date], company, putCall, avg(price) as price

from oldTable

group by [date], company, putCall;

the only difference being the brackets around date which is a reserved word in Access.

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: How can I combine/average values within this table?

Proc sql seems straight forward,

data have;

input date :yymmdd10.     COMPANY $   PUT_CALL $       PRICE;

format date yymmdd10.;

cards;

2001/01/01       XOM                     PUT                10

2001/01/01       XOM                     CALL              12

2001/01/01       XOM                     PUT                11

2001/01/01       XOM                     CALL              13

;

proc sql;

  select DATE , COMPANY, PUT_CALL, mean(price) as price

        from have

   group by DATE , COMPANY, PUT_CALL

   ;

quit;

Haikuo

Solution
‎05-19-2013 04:28 PM
Respected Advisor
Posts: 4,919

Re: How can I combine/average values within this table?

You can use roughly the same query in Access or SAS/SQL :

in SAS

proc sql;

create table newTable as

select date, company, putCall, avg(price) as price

from oldTable

group by date, company, putCall;

quit;

in Access:

create table newTable as

select [date], company, putCall, avg(price) as price

from oldTable

group by [date], company, putCall;

the only difference being the brackets around date which is a reserved word in Access.

PG

PG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 149 views
  • 0 likes
  • 3 in conversation