topic Re: how to select top 100 observation a variable by rank in SAS Programming

how to select top 100 observation a variable by rank

caveman529 — Sun, 27 Oct 2013 23:24:00 GMT

Dear all:

I hope to select the observations with top 100 assets in each year. May I ask how to do it?

Either by sql or proc rank or proc univariate?

The data looks like:

firm	year	asset
1	1997	535
1	1998	453
1	1999	7856
2	1997	87
2	1998	87687
2	1999	452
2	2000	78
2	1997	78
3	1998	986

I tried the following, but it results in sort execution failure:

proc sql;

creassetse table r.want as

select a.*

from r.have as a

left join r.have as b on (a.year=b.year and a.assets <=b.assets)

group by a.year, a.assets

having count(*) <= 100

order by a.year, a.assets desc;

quit;

Thanks !

Message was edited by: Eric Wayne

Re: how to select top 100 observation a variable by rank

PGStats — Mon, 28 Oct 2013 00:45:36 GMT

How about SORT and a DATASTEP? Here, for example is how to get the top 2 assets for each year :

data have;
input firm year asset;
datalines;
1 1997 535
1 1998 453
1 1999 7856
2 1997 87
2 1998 87687
2 1999 452
2 2000 78
2 1997 78
3 1998 986
;

proc sort data=have; by year descending asset; run;

data want;
order = 0;
do until (last.year);
     order + 1;
     set have; by year;
     if order <= 2 then output;
     end;
run;

Re: how to select top 100 observation a variable by rank

cau83 — Fri, 01 Nov 2013 18:52:47 GMT

I believe you should just be able to sort--

proc sort data=dataset out=newdataset(where=(_n_ le 100));

by asset descending;

run;

_n_ is an automatic variable sas creates as a record ID, so proc sort would put the top 100 at the beginning, and then on the new dataset you would only keep the first 100 records.

Re: how to select top 100 observation a variable by rank

PGStats — Fri, 01 Nov 2013 21:22:17 GMT

, that can't work for two reasons:

450 proc sort data=sashelp.cars(where=(_n_<=3));

ERROR: Variable _n_ is not on file SASHELP.CARS.

451 by make horsepower;

452 run;

First, variable _n_ is created only within a datastep, it is not created during other SAS procedures and it is not saved with datasets.

Second, variable _n_ is not reset at the beginning of each BY group, so it would only identify the first observations from the dataset and not those from each BY group.

Re: how to select top 100 observation a variable by rank

caveman529 — Sat, 02 Nov 2013 02:32:58 GMT

Thank you so much !!!

Re: how to select top 100 observation a variable by rank

cau83 — Sat, 02 Nov 2013 21:42:40 GMT

you are correct.

I should have used my other suggestion I was thinking of at the time-- using an obs= option on the to data set-- i actually tested that and it doesn't work either but it was not as obviously wrong and does not produce an error.

Nevertheless, what I just validated to work with only a proc sort is this:

proc sort dataset to=newdataset;

options obs=#ofObsYouWant;

by variable;

run;

Re: how to select top 100 observation a variable by rank

PGStats — Sun, 03 Nov 2013 03:08:54 GMT

It doesn't validate for me.

data test;
do grp = 1, 2;
     do id = 1 to 10;
          output;
          end;
     end;
run;

options obs=3;
proc sort data=test out=top3perGrp;
by grp descending id;
run;

dataset top3perGrp does not contain top 3 id from each grp, as required.

Re: how to select top 100 observation a variable by rank

Haikuo — Sun, 03 Nov 2013 03:44:45 GMT

Besides it does not work for the requirement like PG pointed out, using system options for this kind of task doesn't seem to be a good practice, programmer has to remember where to set it back to default, because you can't get help from error log when the intended output is not achieved.

Re: how to select top 100 observation a variable by rank

cau83 — Mon, 04 Nov 2013 00:55:41 GMT

ahh i missed the implied outputting things by top 'x' of each group in the original post, my bad. that makes the obs or _n_ approach both invalid regardless of whether it's a data step or not.

hai.kuo, I agree but if one uses it they need to always write in the options obs=max at the same time.

sorry for the goose chase everyone.

if i were doing this from scratch I'd do a proc sort and then a data step, but instead of PG Stats' do loop I'd use the by statement in the data step, retain a counter variable and then output only when the counter was below your threshhold-- I tend to use the data/by/counter a lot for other things so that's what first pops into mind.

Re: how to select top 100 observation a variable by rank

Prashant_Ph — Tue, 10 Jan 2017 12:10:47 GMT

data have;
input firm year asset;
datalines;
1 1997 535
1 1998 453
1 1999 7856
2 1997 87
2 1998 87687
2 1999 452
2 2000 78
2 1997 78
3 1998 986
;
proc sort data=have;
by year ;
run;

data mak;
set have;
by year;
if first.year then Top=0;
Top+1;
if Top < 3 ;
run;