topic MAX function in PROC SQL in SAS Procedures

MAX function in PROC SQL

xliu1 — Tue, 16 Jun 2020 18:29:28 GMT

I am using max function to retrieve students' most recent active term and its associated enrollment credits. SAS log did not issue any error message but I keep getting Note: Table Work.Credit_Load created, with 0 rows and 9 columns. I am not sure what is wrong with the code, but each ID on table1 should have multiple enrollment terms even though their most recent active term varies. Anyone could help me with this? Thanks.

proc sql;

create table Credit_Load as
select T1.*,
       E.Credit_Load as MostRecentTerm_HoursEnrolled,
	E.TERMID as Recent_Active_Term

from table1 T1
left join  Enrollments E on TI.ID = E.ID
where E.TERMID = (select max(E1.TERMID) from Enrollments E1
                                   where E1.ID = E.ID);
quit;

Re: MAX function in PROC SQL

Kurt_Bremser — Tue, 16 Jun 2020 18:33:59 GMT

That code won't run at all, you have two incorrect aliases (TI and E1).

Please post the complete log of the SQL step as is by copy/pasting into a </> window.

Re: MAX function in PROC SQL

xliu1 — Tue, 16 Jun 2020 18:40:27 GMT

Here is the original SAS log:

825 proc sql;
826
827 create table ncha_Demo_InactiveStudents as
828 select I.*,
829 E.CURR_TERM_CRS_LOAD as MostRecentTerm_HoursEnrolled,
830 E.TERMID
831
832 from NCHA_PIDM I
833 left join Sid.SID_Enrollments_All_Vw(dbkey=(OASIS_PIDM)) E on I.GOREMAL_PIDM = E.OASIS_PIDM
834 where E.BENCH = 'E' and E.TERMID = (select max(E1.TERMID) from Sid.SID_Enrollments_All_Vw E1
835 where E1.OASIS_PIDM = E.OASIS_PIDM);
NOTE: Table WORK.NCHA_DEMO_INACTIVESTUDENTS created, with 0 rows and 9 columns.

836
837
838
839 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1:16.55
cpu time 0.10 seconds

Re: MAX function in PROC SQL

PGStats — Tue, 16 Jun 2020 18:57:27 GMT

Try starting with something like this:

proc sql;
create table Credit_Load as
select 
	T1.*,
    E.Credit_Load as MostRecentTerm_HoursEnrolled,
	E.TERMID as Recent_Active_Term
from 
	table1 as T1 inner join  
	Enrollments as E on T1.ID = E.ID
group by E.ID
having E.TERMID = max(E.TERMID);
quit;

Re: MAX function in PROC SQL

xliu1 — Tue, 16 Jun 2020 19:30:32 GMT

Thank you! it works out fine.