- /*Q4*/
- %macro processyear(Inputds,Year,Outputds);
- proc sql;
- create table &Outputds as
- select
- Year
- ,StudentID
- ,Score
- ,Avg(Score) as YearAvg
- from &Inputds
- where year=&year
- group by
- Year
- order by
- Score desc
- ;
- quit;
- data &Outputds;
- set &Outputds;
- by descending Score;
- if _N_=1;
- rename StudentID=StudentHighScore;
- drop Score;
- run;
- %mend processyear;
- %processyear(StudentScore,95,Year_Out);
- /*Q5*/
- %MACRO split;
- proc sort data=StudentScore;by StudentId year;run;quit;
- data t1;
- set StudentScore;
- by StudentId year;
- Grp=compress(StudentID||'_'||year);
- if first.year;
- keep StudentID year Grp;
- run;
- proc sql;
- select Grp into:output separated by '|' from t1;
- quit;
- %let cnt=%eval(%sysfunc(count(&output,|))+1);
- %put &output &cnt;
-
- data
- %do i=1 %to &cnt;
- %scan(&output,&i,|)
- %end;
- ;
- set StudentScore;
- %do i=1 %to &cnt;
- %if &i>=2 %then %do;else%end;
- if StudentID="%scan(%scan(&output,&i,|),1,_)" and year=%scan(%scan(&output,&i,|),2,_) then output %scan(&output,&i,|);
- %end;
- ;
- run;
- %MEND split;
- %SPLIT;