Hi,
suppose I have the following table:
stock | date | category | size |
---|---|---|---|
A | 31/12/2010 | 1 | 30 |
A | 31/12/2010 | 0 | 20 |
A | 31/12/2010 | 1 | 40 |
A | 27/11/2011 | 0 | 4 |
A | 27/11/2011 | 1 | 6 |
A | 27/11/2011 | 1 | 8 |
for each stock-date combination, I would like to get the total average size and the average size of category 1:
stock | date | total average size | cat1 average size |
---|---|---|---|
A | 31/12/2010 | 30 | 35 |
A | 27/11/2011 | 6 | 7 |
Thank you!
data have; infile cards expandtabs truncover; input stock $ date : ddmmyy10. cat size; format date ddmmyy10.; cards; A 31/12/2010 1 30 A 31/12/2010 0 20 A 31/12/2010 1 40 A 27/11/2011 0 4 A 27/11/2011 1 6 A 27/11/2011 1 8 ; run; proc sql; create table want as select stock,date,mean(size) as mean_all, (select mean(size) from have where stock=a.stock and date=a.date and cat=1) as mean_1_cat from have as a group by stock,date; quit;
Xia Keshan
Not quite the same format but try a proc means looking at WAYS and TYPE statement.
You could do a single proc sql as well.
Code:-
proc sql;
create table sample1 as
select stock,date, avg(size) as total_average_size
from stock
group by date;
quit;
proc sql;
create table sample2 as |
select stock,date, category,avg(size) as cat1_avg_size
from stock
where category=1
group by date, category;
quit;
proc sql;
create table final as
select a.*, b.cat1_avg_size
from sample1 a, sample2 b
where a.stock=b.stock;
quit;
data have;
infile cards dsd;
informat date ddmmyy10.;
format date ddmmyy10.;
input stock $ date category size;
cards;
A,31/12/2010,1,30
A,31/12/2010,0,20
A,31/12/2010,1,40
A,27/11/2011,0,4
A,27/11/2011,1,6
A,27/11/2011,1,8
;
run;
proc sql;
create table want as
select distinct stock,avg_all,date,avg(size) as avg_one
from(
select distinct *,avg(size) as avg_all
from have
group by stock,date)
where category = 1
group by stock,date;
data have; infile cards expandtabs truncover; input stock $ date : ddmmyy10. cat size; format date ddmmyy10.; cards; A 31/12/2010 1 30 A 31/12/2010 0 20 A 31/12/2010 1 40 A 27/11/2011 0 4 A 27/11/2011 1 6 A 27/11/2011 1 8 ; run; proc sql; create table want as select stock,date,mean(size) as mean_all, (select mean(size) from have where stock=a.stock and date=a.date and cat=1) as mean_1_cat from have as a group by stock,date; quit;
Xia Keshan
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.