Hi All,
I have student's grade. I want want average of student's grade, but
when student ID=102, I want the average of top 3 courses average and
when student ID=103, I want the average of top 4 courses.
Can someone help please. Thanks,
data have;
input ID Course $ Score;
cards;
101 Eng 20
101 Bio 20
101 Math 18
105 Eng 15
105 Math 15
105 Phy 15
102 Eng 15
102 Bio 20
102 Phy 16
102 Arts 12
103 Bio 18
103 Math 20
103 Eng 16
103 Che 15
103 Phy 17
RUN;
Output will be like this:
ID Course Score Average
101 Eng 20 19.3
101 Bio 20 19.3
101 Math 18 19.3
105 Eng 15 15
105 Math 15 15
105 Phy 15 15
102 Eng 15 17
102 Bio 20 17
102 Phy 16 17
102 Arts 12 17
103 Bio 18 18.25
103 Math 20 18.25
103 Eng 16 18.25
103 Che 15 18.25
103 Phy 19 18.25
data have; input ID Course $ Score; cards; 101 Eng 20 101 Bio 20 101 Math 18 105 Eng 15 105 Math 15 105 Phy 15 102 Eng 15 102 Bio 20 102 Phy 16 102 Arts 12 103 Bio 18 103 Math 20 103 Eng 16 103 Che 15 103 Phy 17 ; RUN; data want; n=0;sum=0; do until(last.id); set have; by id notsorted; if not missing(Score) then n+1; if id =102 then do; if n lt 4 then sum+Score; end; else if id =103 then do; if n lt 5 then sum+Score; end; else do; sum+Score; end; end; if id =102 then average=sum/3; else if id =103 then average=sum/4; else average=sum/n; do until(last.id); set have; by id notsorted; output; end; drop n sum; run;
How are those rules defined? Based on data or outside knowledge? What about other IDs?
So, what's the rule for studentId = 101?
@mlogan wrote:
My output sample says everything.
If it did, we wouldn't be asking.
Sort dataset by ID and descending grades
Create a record counter and use that to filter your data set
to only records you want to include in Average.
Calculate average via proc means or sql
Merge results back with table
OR
step1 same as above
Then create an indicator variable (1/0) that denotes if the record contributes to average.
Use SQL to calculate and merge results using indicator variables - sum(indicator*value)/sum(indicator) as average
Assuming your Exceptions data is expressed in a dataset:
data have;
input ID Course $ Score;
cards;
101 Eng 20
101 Bio 20
101 Math 18
105 Eng 15
105 Math 15
105 Phy 15
102 Eng 15
102 Bio 20
102 Phy 16
102 Arts 12
103 Bio 18
103 Math 20
103 Eng 16
103 Che 15
103 Phy 17
;
data exceptions;
input ID nbTop;
datalines;
102 3
103 4
;
proc sort data=have; by ID descending score; run;
data countingScores;
merge have exceptions (in=ex); by ID;
if first.ID then do;
order = 0;
sumScore = 0;
end;
order + 1;
if not (ex and order > nbTop) then sumScore + Score;
if last.ID then do;
meanScore = sumScore / min(order, nbTop);
output;
end;
keep ID meanScore;
run;
proc print data=countingScores noobs; run;
data have; input ID Course $ Score; cards; 101 Eng 20 101 Bio 20 101 Math 18 105 Eng 15 105 Math 15 105 Phy 15 102 Eng 15 102 Bio 20 102 Phy 16 102 Arts 12 103 Bio 18 103 Math 20 103 Eng 16 103 Che 15 103 Phy 17 ; RUN; data want; n=0;sum=0; do until(last.id); set have; by id notsorted; if not missing(Score) then n+1; if id =102 then do; if n lt 4 then sum+Score; end; else if id =103 then do; if n lt 5 then sum+Score; end; else do; sum+Score; end; end; if id =102 then average=sum/3; else if id =103 then average=sum/4; else average=sum/n; do until(last.id); set have; by id notsorted; output; end; drop n sum; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.