topic Re: How to produce min, max and average using Accumulating Variables ? in SAS Procedures

How to produce min, max and average using Accumulating Variables ?

K_Wils15 — Sun, 08 Dec 2019 17:11:59 GMT

Have a final next week and I am not sure how to answer this question:

Assume you have a SAS data set called 'GPA_DATA' that contains the variables STUDENT_NUM SCHOOL GRADE
GENDER and GPA. Write all of the SAS code necessary to find the minimum, maximum and average GPA separately
for each combination of gender, school and grade.
You must use accumulating variables to accomplish all of this (NOT Proc Means).

Here is the code I have so far (I added notes to help me stay oriented). Am I on the right track? Do I need to add anything else? :

data GPA_Data2;
set GPA_DATA;
where STUDENT_NUM ne . and Gender ne '' and Grade ne . and SCHOOL ne '' and GPA ne .;
run;

proc sort data=GPA_DATA2 out=GPA_DATA3;
by STUDENT_NUM SCHOOL GRADE GENDER GPA;
run;

data NEW_GPA_DATA (keep = STUDENT_NUM SCHOOL GRADE GENDER n_gpa min_gpa max_gpa avg_gpa sum_gpa);
set GPA_DATA3;
by STUDENT_NUM SCHOOL GRADE GENDER GPA;
retain min_gpa max_gpa sum_gpa n_gpa;
if first.STUDENT_NUM then do;
min_gpa = GPA;
* Since we sorted in ascending order the first record will have the min GPA.;
max_gpa = GPA; * After this we will look for larger GPAs.;
sum_GPA = 0;
n_GPA = 0;
end;
sum_gpa = sum(sum_gpa, GPA);
n_gpa = sum(n_gpa, 1);
max_gpa = max(max_gpa, GPA);
avg_gpa = sum_gpa / n_gpa;
* Do not have to worry about divide by zero since we added 1 three lines up.;
if last.STUDENT_NUM;
run;

proc print data=NEW_GPA_DATA;
run;

Re: How to produce min, max and average using Accumulating Variables ?

Reeza — Sun, 08 Dec 2019 19:38:37 GMT

You're on the right track, but you're not doing it for the right groups, if the instructions say:

find the minimum, maximum and average GPA separately
for each combination of gender, school and grade.

And remember, although you cannot use PROC MEANS for your final answer you can use it to check your results.

This portion outputs each for each studentNum.

if last.STUDENT_NUM;

You're not doing student level analysis, so you don't need to consider it in your analysis because it identifies unique students. You've left it in as your primary grouper though so that's incorrect.

@K_Wils15 wrote:

Have a final next week and I am not sure how to answer this question:

Assume you have a SAS data set called 'GPA_DATA' that contains the variables STUDENT_NUM SCHOOL GRADE
GENDER and GPA. Write all of the SAS code necessary to find the minimum, maximum and average GPA separately
for each combination of gender, school and grade.
You must use accumulating variables to accomplish all of this (NOT Proc Means).

Here is the code I have so far (I added notes to help me stay oriented). Am I on the right track? Do I need to add anything else? :

data GPA_Data2;
set GPA_DATA;
where STUDENT_NUM ne . and Gender ne '' and Grade ne . and SCHOOL ne '' and GPA ne .;
run;

proc sort data=GPA_DATA2 out=GPA_DATA3;
by STUDENT_NUM SCHOOL GRADE GENDER GPA;
run;

data NEW_GPA_DATA (keep = STUDENT_NUM SCHOOL GRADE GENDER n_gpa min_gpa max_gpa avg_gpa sum_gpa);
set GPA_DATA3;
by STUDENT_NUM SCHOOL GRADE GENDER GPA;
retain min_gpa max_gpa sum_gpa n_gpa;
if first.STUDENT_NUM then do;
min_gpa = GPA;
* Since we sorted in ascending order the first record will have the min GPA.;
max_gpa = GPA; * After this we will look for larger GPAs.;
sum_GPA = 0;
n_GPA = 0;
end;
sum_gpa = sum(sum_gpa, GPA);
n_gpa = sum(n_gpa, 1);
max_gpa = max(max_gpa, GPA);
avg_gpa = sum_gpa / n_gpa;
* Do not have to worry about divide by zero since we added 1 three lines up.;
if last.STUDENT_NUM;
run;

proc print data=NEW_GPA_DATA;
run;

Re: How to produce min, max and average using Accumulating Variables ?

K_Wils15 — Sun, 08 Dec 2019 19:52:12 GMT

Thank you!