Prev: Update data
Next: Using Excel libname engine to export multiple datasets
From: les_daniel on 23 Jul 2010 05:35
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
From: Patrick on 23 Jul 2010 05:42
Hi Daniel

If you search this forum you will find heaps of threads dealing with
top/top1/top10 questions.

It also would help if you could provide sample data (a data step
creating a sample data set) and then show how the output should look
like.

HTH
Patrick
From: KLR on 23 Jul 2010 08:33
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.
From: les_daniel on 24 Jul 2010 13:58
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.

From: Arthur Tabachneck on 24 Jul 2010 14:23
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 -

 |  Next  |  Last
Pages: 1 2
Prev: Update data
Next: Using Excel libname engine to export multiple datasets