- /* create a sample dataset */
- data yearsales;
- do year=1992 to 1994;
- do I = 1 to ranuni(12345)*100 + 50;
- sale = int(ranuni(123456)*120);
- output;
- end; end;
- run;
- proc sql;
- create table yearsale_1 as
- select year, sale, count(*) as N_by_year
- from yearsales
- group by year
- order by year, sale desc;
- quit;
- data yearsale_top20;
- set yearsale_1;
- by year;
- if first.year = 1 then n=1;
- else n+1;
- if n/N_by_year*100 <= 20 then output;
- run;
- proc sql;
- create table want as
- select year, avg(sale) as top20Mean
- from yearsale_top20
- group by year
- order by year;
- quit;