topic Re: PROC SQL SELECT question in SAS Procedures

PROC SQL SELECT question

jrsm21 — Sat, 06 Apr 2019 22:49:09 GMT

I am new to SAS and have been working on some exercises, but don't know if my code is correct. I have three data sets and need to combine all three into one and then create a new one with only unique records.

Here are the variable definitions and my code:

SAS Dataset Name: STUDENTS
Column Name	Type	Format	
First_name	Text	$30.	
Last_name	Text	$30.	
DOB		Text	mmddyy10.
StudentID	Text	$5.	

SAS Dataset Name: COURSES
Column Name	Type	Format	
StudentID	Text	$5.	
CourseID	Text	$8.	
Course_name	Text	$30.
Begin_date	Number	mmddyy10.
End_date	Number	mmddyy10.
TestID	        Text	$10.	

SAS Dataset Name: GRADES
Column Name	Type	Format	
TestID		Text	$10.	
Test_type	Text	$5.	
Grade		Number  +3.	
Grade_date	Number	Mmddyy10.	
	

// Put all results from the three tables into one table

PROC SQL;
CREATE TABLE All_Grades AS 
      SELECT Students.*, Courses.*, Grades.* 
      FROM Students, Courses, Grades
      WHERE Students.StudentID = Courses.StudentID AND Courses.TestID = Grades.TestID;
quit;

// From the previous combined table, select unique student IDs with their the earliest test grade expressed as a year

PROC SQL; 
     CREATE TABLE Earliest_Test AS 
     SELECT DISTINCT StudentID, TestID, MIN(Test_date) num AS test_year format=YEAR4
     FROM All_Grades
     ORDER BY StudentID ; 
quit;

Re: PROC SQL SELECT question

Reeza — Sat, 06 Apr 2019 22:58:46 GMT

So what is your question? Do you have some reason to believe the code is incorrect? Are you getting errors or issues with the results?

Here's some improvement suggestions but I suspect your answer is fine.

Listing tables separated via a comma is a cross join, which is filtered via the WHERE. You may want to explicitly define the type of joins you want instead of letting SAS figure it out. Your log may indicate that as well - check if there's a note in it.
Check the number of records and determine how many should have at the end and verify that's what you're getting. I suspect you need as many records as in GRADES in your final data set but that could depend on the logic of how the data is collected, which I do not know.
I think format=year4 should have a period at the end to define the format, but if it's causing issues I'm wrong.

Given lack of data or context, I'm guessing a lot here.

@jrsm21 wrote:

Here are the variable definitions and my code:

SAS Dataset Name: STUDENTS
Column Name	Type	Format	
First_name	Text	$30.	
Last_name	Text	$30.	
DOB		Text	mmddyy10.
StudentID	Text	$5.	

SAS Dataset Name: COURSES
Column Name	Type	Format	
StudentID	Text	$5.	
CourseID	Text	$8.	
Course_name	Text	$30.
Begin_date	Number	mmddyy10.
End_date	Number	mmddyy10.
TestID	        Text	$10.	

SAS Dataset Name: GRADES
Column Name	Type	Format	
TestID		Text	$10.	
Test_type	Text	$5.	
Grade		Number  +3.	
Grade_date	Number	Mmddyy10.	
	

// Put all results from the three tables into one table

PROC SQL;
CREATE TABLE All_Grades AS 
      SELECT Students.*, Courses.*, Grades.* 
      FROM Students, Courses, Grades
      WHERE Students.StudentID = Courses.StudentID AND Courses.TestID = Grades.TestID;
quit;

// From the previous combined table, select unique student IDs with their the earliest test grade expressed as a year

PROC SQL; 
     CREATE TABLE Earliest_Test AS 
     SELECT DISTINCT StudentID, TestID, MIN(Test_date) num AS test_year format=YEAR4
     FROM All_Grades
     ORDER BY StudentID ; 
quit;

Re: PROC SQL SELECT question

PGStats — Sun, 07 Apr 2019 04:16:09 GMT

To me, it looks like that last step should be

PROC SQL; 
     CREATE TABLE Earliest_Grade AS 
     SELECT StudentID, TestID, grade, year(Grade_date) AS grade_year 
     FROM All_Grades
     GROUP BY StudentID
     HAVING grade_date = min(grade_date); 
quit;

and the comments syntax should be converted into proper SAS.

Re: PROC SQL SELECT question

Reeza — Sun, 07 Apr 2019 04:21:54 GMT

'unique' records is ambiguous in this situation. Is a unique record one per test, one per course or one per student?

Re: PROC SQL SELECT question

jrsm21 — Sun, 07 Apr 2019 04:22:13 GMT

Thanks!

Re: PROC SQL SELECT question

jrsm21 — Sun, 07 Apr 2019 04:36:48 GMT

Only one course record for each student. It should be the test record with the earliest test_date.
Only the variables StudentID, CourseID, Test_date, and Grade should be included in Earliest_Test data table. The test_year variable should contain the test_date year.

Re: PROC SQL SELECT question

Reeza — Mon, 08 Apr 2019 04:03:48 GMT

Regardless of test type?