Prev: Update data
Next: Using Excel libname engine to export multiple datasets
From: les_daniel on 26 Jul 2010 04:52
Hi Art,
thks very much ,that helps if i proc print the data,and
it returns me what i want exactly in the desired order... I have never
considered the problem of handling ties as i raised the query as a
setup example only...

So, if i were to use proc tabulate as my results output...i would have
to do a descending sort 1st and then use the order=data option in proc
tabulate, rite,else it would still list all students?

Alternatively, i also try proc sql though this would give me all the
students within the different classes, as i think i may need one or
two more statements within the procedure to settle the extraction of
top 10 scores of students in each different clases. as shown below ..
PROC SQL;
CREATE TABLE GRP1 AS
SELECT
class , Mean_Score <etc>...... ,count(*) as count
from work.tsort
order by class , Mean_Score desc;

QUIT;


Secondly,on the question of ties as you pointed out, if say i used
ties=low to resolve any same values from the ranking, how do i further
solve the issue, using any method, (sql, data step, or proc rank) ?


Thks anyway for the sharing...

Regds,Dan..






On Jul 25, 2:23 am, Arthur Tabachneck <art...(a)netscape.net> wrote:
> Daniel,
>
> I think that the following does what you want, but you never mentioned
> what you want to do if there is a tie at the 10th place:
>
> data t1 (keep=class name Mean_Score);
>   set sashelp.class;
>   class='A1';
>   mean_score=height;
>   output;
>   class='B1';
>   mean_score=weight;
>   output;
> run;
>
> proc sort data=t1;
>   by class descending Mean_Score;
> run;
>
> data tset1 (where=(counta le 10));
>   set t1;
>   by class;
>   if first.class then counta=0;
>   counta+1;
> run;
>
> HTH,
> Art
> -------------
> On Jul 24, 1:58 pm, les_daniel <leonhardye...(a)gmail.com> wrote:
>
>
>
> > Hi,
> >     thks for the suggestions....these are my attempt to do this query.
>
> > /* Cod denote the Student ID   */
>
> > Data tset1;
> > set t1;
> > by class descending Mean_Score;
> > retain counta 0;
> > if Cod not in (“ “) then CNT + 1;
> > if first.class then do;
> >         counta +1;
> > end;
> > run;
>
> > data tset2;
> > set tset1;
> > by class descending Mean_Score;
> > if first.class then do;
> > if CNT ge 0 then output;
> > if _N_ <=10 then output;
> > end;
> > run;
>
> > proc tabualte;
> > .................
> > run;
>
> > My resultant output;.<as seen from extract in dataset>
> > Class    Cod(Stud. ID) Mean_Score       Count Counta
> > A1       1      221                          92 1       1
> >          2      112                          89 1       2
> >          3      333                          85 1       3
> >          10     005                       72    1       10
> > B1       1                                   91 2       11
> >          2                                   87 2       12
> >          3                                   85 2       13
> >         10                                   84 2       20
> > C1       1                                              3       21
> >          2                                              3       22
> >          3                                              3       23
> >         10                                              3       30
>
> > .........................-----------------------
>
> > As seen, i wanted to list only the top 10 students of each class with
> > the descending top 10 mean scores in each different class...the
> > "Counta" variable created produces a running number say ...from 1 to
> > 20 in each class, but say, iwhen it comes to Class B/C. and so on, the
> > counta value reflected should  still be numbered as '1',2,3,...10. so
> > that it is seen as a ranking of sorts....
>
> > It is like in Excel, at each change in Class,, list the top 10
> > students with the highest scores in each different class, to put it
> > simply...
>
> > Cos i see it as a counter  method to list and keep only the top 10
> > from  each different classes of students...
>
> > Hope you understand what i mean...as
>
> > Kindly enlighten me if possible ,if there is a way though..
>
> > Sorry, for any long-windedness
>
> > Thks,
>
> > Regards,
> > Daniel
>
> > On Jul 23, 8:33 pm, KLR <k.robe...(a)ucas.ac.uk> wrote:
>
> > > On 23 July, 10:35, les_daniel <leonhardye...(a)gmail.com> wrote:
>
> > > > Hi,
> > > >       I m writing in regards to a sorting –by-descending problem  for
> > > > the top 10 scores of students ,say in each different class;
>
> > > > Most importantly,  and secondly, is how to extract only every top 10
> > > > scores of the students in each different class as shown below:
>
> > > > Class        Name of Student  Avg   Median
> > > > A1      1
> > > >         2
>
> > > >         10
> > > > B1      1
> > > >         2
>
> > > >         10
> > > > C1
>
> > > > So far, I have managed to obtain the scores of all students in each
> > > > different classes, in descending  order in average(mean) values, via
> > > > both proc sql methods, or sort the dataset before proc tabulate….but
> > > > I  fail to extract the top 10 scores of student from each different
> > > > class, so I  did the troublesome way of doing the sorting and deleting
> > > > of unwanted rows after the 10th row of each different class in
> > > > Microsoft Excel…
>
> > > > Would appreciate if it can be done foremostly, in SAS programming, to
> > > > improve the efiiciency of output….Thks…
>
> > > > Regds,
>
> > > > Daniel
>
> > > Sort out classes into separate datasets by grade, retaining the first
> > > 10 observations, then merge them back into one dataset.- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

From: Richard A. DeVenezia on 26 Jul 2010 08:05
On Jul 23, 5:35 am, les_daniel <leonhardye...(a)gmail.com> wrote:
> Hi,
>       I m writing in regards to a sorting –by-descending problem  for
> the top 10 scores of students ,say in each different class;
>
> Most importantly,  and secondly, is how to extract only every top 10
> scores of the students in each different class as shown below:
>
> Class        Name of Student  Avg   Median
> A1      1
>         2
>
>         10
> B1      1
>         2
>
>         10
> C1
>
> So far, I have managed to obtain the scores of all students in each
> different classes, in descending  order in average(mean) values, via
> both proc sql methods, or sort the dataset before proc tabulate….but
> I  fail to extract the top 10 scores of student from each different
> class, so I  did the troublesome way of doing the sorting and deleting
> of unwanted rows after the 10th row of each different class in
> Microsoft Excel…
>
> Would appreciate if it can be done foremostly, in SAS programming, to
> improve the efiiciency of output….Thks…

Dan:

"efiiciency of output" is more a study of Tufte than SAS
programming :)

You can use Proc RANK descending, but be sure to understand how the
procedure rates ties.
You can also use a DO over BY group in a DATA Step.

----------
data scores;
do class = 3 to 1 by -1;
do _n_ = 1 to 40;
id + 1;
score = floor(30*ranuni (1234));
output;
end;
end;
run;

proc sort data=scores out=scores2;
by class;
run;

proc rank descending data=scores2 out=top10 (where=(rank<11));
by class;
var score;
ranks rank;
run;

proc sort data=top10;
by class rank;
run;



proc sort data=scores out=scores3;
by class descending score;
run;

data top10otherway;
do _n_ = 1 by 1 until (last.class);
set scores3;
by class;
if (_n_ <= 10) then _x = score;
if (_n_ <= 10) or (score = _x) then output;
end;
drop _x;
run;
----------

Richard A. DeVenezia
http://www.devenezia.com
First  |  Prev  | 
Pages: 1 2
Prev: Update data
Next: Using Excel libname engine to export multiple datasets