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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.