@andreas_lds  Yes, all of the GPAs are missing       Do you mean adding other =. instead of library Data like this?   proc format library other=.; invalue grade 'A'=4 'B'=3 'C'=2 'D'=1 'F'=0; run; proc sql; create table Data.gpa as select t1.*, t2.GPA from Data.master_after2001 t1 left join ( select studentID, (sum(input(grade,grade.))/count(courseID)) as GPA from Data.grades group by studentID ) t2 on t1.studentID = t2.studentID; quit; ... View more

@sustagens  I am now showing blanks in the GPA column. I am getting a few notes that are saying t1.studentID = t2.studentID is invalid:   Error log:     82 proc sql; 83 create table Data.gpa as 84 select t1.*, t2.GPA 85 from Data.master_after2001 as t1 86 left join ( select studentID, (sum(input(grade,grade.))/count(courseID)) as GPA 87 from Data.grades 88 group by studentID ) as t2 89 on t1.studentID = t2.studentID; NOTE: Invalid string. NOTE: Invalid argument to function INPUT. Missing values may be generated. NOTE: Table DATA.GPA created, with 3427 rows and 6 columns.   90 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.11 seconds cpu time 1.19 seconds         Here is my code:     proc format library=Data; invalue grade 'A'=4 'B'=3 'C'=2 'D'=1 'F'=0; run; proc sql; create table Data.gpa as select t1.*, t2.GPA from Data.master_after2001 as t1 left join ( select studentID, (sum(input(grade,grade.))/count(courseID)) as GPA from Data.grades group by studentID ) as t2 on t1.studentID = t2.studentID; quit; ... View more

@sustagens  Thanks! It came out great! The only thing is the the GPA is calculated by the sum divided by the number of courses taken (number of grades provided). How can I incorporate this in? ... View more

@Patrick  and @sustagens  here is the second file. It didnt attach to the first. ... View more

@Patrick  and @sustagens  thanks for the input! I have attached the data for reference. ... View more

I have a few thousand grades as well. ... View more

@sustagensthis would be awesome if I only had a few students, however, I have a few thousand students in my file (I just sent a screenshot of a few to get the layout of the data). It there a way to make it so I don't have to type out every row? ... View more

Both the new tables were giving me that output. My first original file of students (Data.master_After2001) has 5,000 students my 2nd original file of grades (Data.grades) has over 1,000,000 lines as each student has more then one grade. I am looking to convert the grades to A=4, B=3, C=2, D=1, and F=0, sum them up for each student then divide them by the number of courses taken (how many grades supplied for each student) to find the gpa for each student. In total the file should be 5,000 rows but is currently over 1,000,000 with the code provided.    data Data.grades_recoded; set Data.grades; if grade='F' then grade_value=0; else if grade='D' then grade_value = 1; else if grade='C' then grade_value = 2; else if grade='B' then grade_value = 3; else if grade='A' then grade_value = 4; RUN; PROC SORT DATA=Data.grades_recoded; by studentID; RUN; PROC SORT DATA=Data.master_After2001; by studentID; RUN; data Data.master_GPA; merge Data.grades_recoded Data.master_After2001 (keep = studentID gender); by studentID; RUN; PROC MEANS DATA=Data.master_GPA STACKODS N MEAN MAX MEDIAN MIN; class gender; var grade_value; ods output summary=SummaryStats; RUN;   ... View more

Thanks for the reply! How can I make it so that the GPA is another row in the Data.master_After2001 file? Your code is giving me a means table but I want the gpa to apart of the first file of 5,000 students. This is what your code is giving me an output of:   ... View more