## How can I combine/average values within this table?

Solved
Occasional Contributor
Posts: 8

# 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
Posts: 5,540

## 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

All Replies
Posts: 3,167

## 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
Posts: 5,540

## 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.