/*Attrition Cost Program*/
/*For the entire higher education system without a sector breakdown*/
/*Unit Cost: Instructional Cost per Credit*/

/*------------------------------------------Instruction-------------------------------------------------------*/

/*Step 1: Enter the location you would like to store the output file*/
libname sasout "C:\Documents and Settings\Education1\Desktop\AttritionCost"; 

/*Step 2: Enter the location of year1 enrollment file after "datafile="*/
proc import datafile="C:\Documents and Settings\Education1\Desktop\AttritionCost\Year1_Enrollment.csv"
     out=mydata1
     dbms=csv
     replace;
     getnames=yes;
	 datarow=2;
run;

/*Step 3: Enter the location of year2 enrollment file after "datafile="*/
proc import datafile='C:\Documents and Settings\Education1\Desktop\AttritionCost\Year2_Enrollment.csv'
     out=mydata2
     dbms=csv
     replace;
     getnames=yes;
	 datarow=2;
run;

/*Step 4: Enter the location of degree file after "datafile="*/
proc import datafile="C:\Documents and Settings\Education1\Desktop\AttritionCost\Degree_File.csv"
     out=mydata3
     dbms=csv
     replace;
     getnames=yes;
	 datarow=2;
run;

proc sql;
   create table sasout.FiscalData
		(StuLevel num format=best8. informat = best8.,
 		UnitCost num format=best8. informat = best8.); 
run;

/*Step 5: Enter the unit cost of instruction in the following order - do not use 1000 separater or $ sign*/
			/*1 - Instructional Cost per Credit for Lower-level Students*/
			/*2 - Instructional Cost per Credit for Upper-level Students*/
Proc SQL;
	Insert into sasout.FiscalData
	Values (1,100/*1*/); /*the second value is the instructional cost - do not touch the first value*/ 

	Insert into sasout.FiscalData 
	Values (2,200/*2*/); /*the second value is the instructional cost - do not touch the first value*/
quit;


/*Step 6: Run the SAS program */

/*----------------------------------------------End of Instruction--------------------------------------------*/

/*Convert the data type of ID from numeric into character*/
Proc SQL; 
Create Table SASOUT.mydata1 AS
Select Put(ID, 12.) AS ID
, AcademicYear, CalendarYear,Sector, Term, TermCredit, Overall_Earned, FirstYear from mydata1;
run;

Proc SQL; 
Create Table SASOUT.mydata2 AS
Select Put(ID, 12.) AS ID
, AcademicYear, CalendarYear,Sector, Term, TermCredit, Overall_Earned, FirstYear from mydata2;
run;

Proc SQL; 
Create Table SASOUT.mydata3 AS
Select Put(ID, 12.) AS ID
, AcademicYear, DegreeType from mydata3;
run;


/*Base Year HC*/

Proc SQL;
Create Table SASOUT.Attrition01 AS
Select 
COUNT(Distinct ID) AS Base_HC
, SUM (TermCredit) AS TotalCredits
, CASE WHEN AcademicYear-FirstYear+1 >10 THEN 10
ELSE AcademicYear - FirstYear+1 END AS StuLevel
from SASOUT.mydata1
Group by StuLevel;
run;

/*Retention HC*/
Proc SQL;
Create Table SASOUT.Attrition02 AS
Select 
Case WHEN mydata1.AcademicYear - mydata1.FirstYear +1 > 10 THEN 10
ELSE mydata1.AcademicYear - mydata1.FirstYear+1 END AS StuLevel
, Count(Distinct mydata1.ID) AS Retain_HC
From SASOUT.mydata1 Inner Join SASOUT.mydata2
on mydata1.ID = mydata2.ID
Group by StuLevel
Order by StuLevel;
run;

/*Graduate HC*/
Proc SQL;
Create Table SASOUT.Attrition03A AS
Select 
Case WHEN mydata1.AcademicYear - mydata1.FirstYear+1 > 10 THEN 10
ELSE mydata1.AcademicYear-mydata1.FirstYear+1 END AS StuLevel
, COUNT(Distinct CASE WHEN mydata1.ID=mydata3.ID
THEN mydata1.ID ELSE "" END) AS Grad_HC1
from SASOUT.mydata1 Left Outer Join SASOUT.mydata3
on mydata1.ID = mydata3.ID
Group by StuLevel
Order by StuLevel;
run;

/*Graduate HC and Transferred*/
Proc SQL;
Create Table SASOUT.Attrition03B AS
Select 
Case WHEN mydata1.AcademicYear - mydata1.FirstYear+1 > 10 THEN 10
ELSE mydata1.AcademicYear-mydata1.FirstYear+1 END AS StuLevel
, COUNT(Distinct CASE WHEN mydata1.ID=mydata2.ID
and mydata1.ID=mydata3.ID THEN mydata1.ID ELSE "" END) AS Grad_Trans_HC
from SASOUT.mydata1 Left Outer Join SASOUT.mydata3
on mydata1.ID = mydata3.ID
Left Outer Join SASOUT.mydata2
on mydata1.ID = mydata2.ID
Group by StuLevel
Order by StuLevel;
run;

/*Graduate HC minus Transferred*/
Proc SQL;
Create Table SASOUT.Attrition03 AS
Select 
Attrition03A.StuLevel
, Grad_HC1
, Grad_Trans_HC
, Grad_HC1 - Grad_Trans_HC AS Grad_HC
from SASOUT.Attrition03A Left Outer Join SASOUT.Attrition03B
on Attrition03A.StuLevel = Attrition03B.StuLevel
Group by Attrition03A.StuLevel
Order by Attrition03A.StuLevel;
run;


/*Stopout Returning*/

Proc SQL;
Create Table SASOUT.Attrition04 AS
Select COUNT(Distinct ID) AS Stopout_HC
from 
(Select mydata2.ID
, MAX(CASE WHEN mydata2.ID = mydata1.ID 
THEN 0 ELSE 1 END) AS Stopout_HC
from SASOUT.mydata2 Left Outer Join SASOUT.mydata1
on mydata2.ID = mydata1.ID
Where mydata2.AcademicYear-mydata2.FirstYear ^= 0
Group by mydata2.ID)X
Where Stopout_HC = 1;
run;

/*Stopout base*/
Proc SQL;
Create table SASOUT.Attrition05 AS
Select COUNT(Distinct ID) AS Stopout_base
From SASOUT.Mydata1;
run;

/*Stopout rate*/
Proc SQL;
Create table SASOUT.Attrition06 AS
Select 
Stopout_hc/Stopout_base AS Stopout_pct
From SASOUT.Attrition04 Cross Join  SASOUT.Attrition05;
run;

/*Retention, Graduation, Stopout Summary*/
Proc SQL;
Create table SASOUT.Attrition07 AS
Select Attrition01.StuLevel
, Attrition01.Base_HC
, Attrition01.TotalCredits
, Attrition01.TotalCredits/Attrition01.Base_HC AS AVG_Credit
, Attrition02.Retain_HC
, Attrition03.Grad_HC
, Retain_HC/Base_HC AS Retention_pct
, Grad_HC/Base_HC AS Grad_pct
, stopout_pct
From SASOUT.Attrition01 Inner Join SASOUT.Attrition02
on Attrition01.StuLevel = Attrition02.StuLevel
Inner Join SASOUT.Attrition03
on Attrition01.StuLevel = Attrition03.StuLevel
Cross Join SASOUT.Attrition06;
run;

/*Student Headcount in Year 2*/
Proc SQL;
Create table SASOUT.Attrition08 AS
Select 
CASE WHEN Mydata2.AcademicYear-
Mydata2.FirstYear+1 > 10 THEN 10
ELSE Mydata2.AcademicYear-
Mydata2.FirstYear+1 END AS StuLevel
, COUNT(Distinct Mydata2.ID) AS Yr2_HC
, Sum(TermCredit) AS Yr2_TotalCredits
, Sum(TermCredit)/COUNT(Distinct Mydata2.ID) AS Yr2_AVG_Credits
From SASOUT.Mydata2
Group by StuLevel
Order by StuLevel;
run;

/*Iteration 1: HC in Projection Year 2*/
Proc SQL;
Create table SASOUT.Attrition09 AS
Select Attrition08.StuLevel
, Attrition08.Yr2_HC
, Attrition08.Yr2_HC*Retention_pct AS Retained2
, Attrition08.Yr2_HC*Grad_pct AS Graduated2
, Attrition08.Yr2_HC*stopout_pct AS Adjusted2
, Attrition08.Yr2_HC*(1 - Retention_pct - Grad_pct - Attrition07.stopout_pct) AS Yr2_dropout
, Case WHEN Attrition08.StuLevel + 1 < 10 THEN Attrition08.StuLevel + 1 ELSE 10 END AS StuLevel2 
, Attrition08.Yr2_HC - Attrition08.Yr2_HC*(1 - Retention_pct - Grad_pct - Attrition07.stopout_pct) - Attrition08.Yr2_HC*Grad_pct AS Yr2_Enrolled
from SASOUT.Attrition08 Inner Join SASOUT.Attrition07 
on Attrition08.StuLevel = Attrition07.StuLevel
Inner Join SASOUT.Attrition01 
on Attrition08.StuLevel = Attrition01.StuLevel;
run;

/*Iteration 2: HC in Projection Year 3*/
Proc SQL;
Create table SASOUT.Attrition10 AS
Select Attrition09.* 
, Attrition09.Yr2_Enrolled*Retention_pct AS Retained3
, Attrition09.Yr2_Enrolled*Grad_pct AS Grads3
, Attrition09.Yr2_Enrolled*stopout_pct AS Adjusted3
, Attrition09.Yr2_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) AS Yr3_dropout
, CASE WHEN Attrition09.StuLevel2+1< 10 THEN Attrition09.StuLevel2+1 ELSE 10 END AS StuLevel3
, Attrition09.Yr2_Enrolled - Attrition09.Yr2_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) - Attrition09.Yr2_Enrolled*Grad_pct AS Yr3_Enrolled
from SASOUT.Attrition09 Inner Join SASOUT.Attrition07 
on Attrition09.StuLevel2 = Attrition07.StuLevel;
run;

/*Iteration 3: HC in Projection Year 4*/
Proc SQL;
Create table SASOUT.Attrition11 AS
Select Attrition10.* 
, Attrition10.Yr3_Enrolled*Retention_pct AS Retained4
, Attrition10.Yr3_Enrolled*Grad_pct AS Grads4
, Attrition10.Yr3_Enrolled*stopout_pct AS Adjusted4
, Attrition10.Yr3_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) AS Yr4_dropout
, CASE WHEN Attrition10.StuLevel3+1< 10 THEN Attrition10.StuLevel3+1 ELSE 10 END AS StuLevel4
, Attrition10.Yr3_Enrolled - Attrition10.Yr3_Enrolled*(1-Retention_pct - Grad_pct-stopout_pct) - Attrition10.Yr3_Enrolled*Grad_pct AS Yr4_Enrolled
from SASOUT.Attrition10 Inner Join SASOUT.Attrition07 
on Attrition10.StuLevel3 = Attrition07.StuLevel;
run;

/*Iteration 4: HC in Projection Year 5*/
Proc SQL;
Create table SASOUT.Attrition12 AS
Select Attrition11.* 
, Attrition11.Yr4_Enrolled*Retention_pct AS Retained5
, Attrition11.Yr4_Enrolled*Grad_pct AS Grads5
, Attrition11.Yr4_Enrolled*stopout_pct AS Adjusted5
, Attrition11.Yr4_Enrolled*(1-Retention_pct - Grad_pct-stopout_pct) AS Yr5_dropout
, CASE WHEN Attrition11.StuLevel4+1< 10 THEN Attrition11.StuLevel4+1 ELSE 10 END AS StuLevel5
, Attrition11.Yr4_Enrolled - Attrition11.Yr4_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) - Attrition11.Yr4_Enrolled*Grad_pct AS Yr5_Enrolled
from SASOUT.Attrition11 Inner Join SASOUT.Attrition07 
on Attrition11.StuLevel4 = Attrition07.StuLevel;
run;

/*Iteration 5: HC in Projection Year 6*/
Proc SQL;
Create table SASOUT.Attrition13 AS
Select Attrition12.* 
, Attrition12.Yr5_Enrolled*Retention_pct AS Retained6
, Attrition12.Yr5_Enrolled*Grad_pct AS Grads6
, Attrition12.Yr5_Enrolled*stopout_pct AS Adjusted6
, Attrition12.Yr5_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) AS Yr6_dropout
, CASE WHEN Attrition12.StuLevel5+1< 10 THEN Attrition12.StuLevel5+1 ELSE 10 END AS StuLevel6
, Attrition12.Yr5_Enrolled - Attrition12.Yr5_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) - Attrition12.Yr5_Enrolled*Grad_pct AS Yr6_Enrolled
from SASOUT.Attrition12 Inner Join SASOUT.Attrition07 
on Attrition12.StuLevel5 = Attrition07.StuLevel;
run;

/*Iteration 6: HC in Projection Year 7*/
Proc SQL;
Create table SASOUT.Attrition14 AS
Select Attrition13.* 
, Attrition13.Yr6_Enrolled*Retention_pct AS Retained7
, Attrition13.Yr6_Enrolled*Grad_pct AS Grads7
, Attrition13.Yr6_Enrolled*stopout_pct AS Adjusted7
, Attrition13.Yr6_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) AS Yr7_dropout
, CASE WHEN Attrition13.StuLevel6+1< 10 THEN Attrition13.StuLevel6+1 ELSE 10 END AS StuLevel7
, Attrition13.Yr6_Enrolled - Attrition13.Yr6_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) - Attrition13.Yr6_Enrolled*Grad_pct  AS Yr7_Enrolled
from SASOUT.Attrition13 Inner Join SASOUT.Attrition07 
on Attrition13.StuLevel6 = Attrition07.StuLevel;
run;

/*Iteration 7: HC in Projection Year 8*/
Proc SQL;
Create table SASOUT.Attrition15 AS
Select Attrition14.* 
, Attrition14.Yr7_Enrolled*Retention_pct AS Retained8
, Attrition14.Yr7_Enrolled*Grad_pct AS Grads8
, Attrition14.Yr7_Enrolled*stopout_pct AS Adjusted8
, Attrition14.Yr7_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct) AS Yr8_dropout
, CASE WHEN Attrition14.StuLevel7+1< 10 THEN Attrition14.StuLevel7+1 ELSE 10 END AS StuLevel8
, Attrition14.Yr7_Enrolled - Attrition14.Yr7_Enrolled*(1-Retention_pct - Grad_pct - stopout_pct)  - Attrition14.Yr7_Enrolled*Grad_pct AS Yr8_Enrolled
from SASOUT.Attrition14 Inner Join SASOUT.Attrition07 
on Attrition14.StuLevel7 = Attrition07.StuLevel;
run;

/*Attrition Summary*/
Proc SQL;
Create table SASOUT.Attrition16 AS
Select StuLevel, Yr2_HC,
Yr2_dropout+Yr3_dropout+Yr4_dropout+Yr5_dropout+Yr6_dropout+Yr7_dropout+Yr8_dropout AS Dropout_total
, (Yr2_dropout+Yr3_dropout+Yr4_dropout+Yr5_dropout+Yr6_dropout+Yr7_dropout+Yr8_dropout)
/ Yr2_HC AS Attrition_Rate
from SASOUT.Attrition15;

run;



/*FTE students in Year 2 by student level*/
Proc SQL;
Create table SASOUT.Attrition17 AS
Select StuLevel, COUNT(ID) AS HC
, SUM(AttemptedCredit) AS CreditHours
, SUM(AttemptedCredit)/COUNT(ID) AS AVGCrdt
, Case WHEN CumCredit>60 THEN 2
Else 1 END AS StuClass
From
(Select ID
, CASE WHEN AcademicYear - FirstYear+1>10 THEN 10
ELSE AcademicYear - FirstYear+1 END AS StuLevel
, SUM(TERMCredit) AS AttemptedCredit
, Min(Overall_EARNED) AS CumCredit
from SASOUT.Mydata2
Group by ID,StuLevel)X
Group by StuLevel, StuClass
Order by StuLevel, StuClass;
run;

/*Results*/
Proc SQL;
Create Table SASOUT.Attrition18 AS
Select 
'Total' AS StuLevel
, SUM(AttritionCost) format dollar15.0 AS AttritionCost
, SUM(TotalCost) format dollar15.0 AS TotalCost 
, SUM(AttritionCost)/SUM(TotalCost) format percent5.1 AS AttritionCost_PCT
From
(Select Attrition17.* 
, Attrition_Rate
, HC*Attrition_Rate*AVGCrdt AS TotCredit
, HC*Attrition_Rate*AVGCrdt*UnitCost AS AttritionCost
, HC*AVGCrdt*UnitCost AS TotalCost
from SASOUT.Attrition17
Inner Join SASOUT.Attrition16
on Attrition17.StuLevel = Attrition16.StuLevel
Inner Join sasout.FiscalData
on Attrition17.StuClass = FiscalData.StuLevel)X

Union All

Select 
Put(StuLevel,z5.)
, SUM(AttritionCost) format dollar15.0 AS AttritionCost
, SUM(TotalCost) format dollar15.0 AS TotalCost 
, SUM(AttritionCost)/SUM(TotalCost) format percent5.1 AS AttritionCost_PCT
From
(Select Attrition17.* 
, Attrition_Rate
, HC*Attrition_Rate*AVGCrdt AS TotCredit
, HC*Attrition_Rate*AVGCrdt*UnitCost AS AttritionCost
, HC*AVGCrdt*UnitCost AS TotalCost
from SASOUT.Attrition17
Inner Join SASOUT.Attrition16
on Attrition17.StuLevel = Attrition16.StuLevel
Inner Join sasout.FiscalData
on Attrition17.StuClass = FiscalData.StuLevel)X
Group by StuLevel;
run;

/*Reproduce Retention and Graduation Summary Table*/
/*Add Total Values*/

Proc SQL;
Create Table SASOUT.Attrition07A AS 
Select Put(StuLevel,z5.) AS StuLevel
, Base_HC, TotalCredits, AVG_Credit, Retain_HC
, Grad_HC, Retention_pct, Grad_pct, Stopout_pct
from SASOUT.Attrition07

Union All

Select 'Total'
, Sum(Base_HC), Sum(TotalCredits), Sum(TotalCredits)/SUM(BASE_HC), Sum(Retain_HC)
, Sum(Grad_HC), SUM(Retain_HC)/Sum(Base_HC), SUM(Grad_HC)/Sum(Base_HC), AVG(Stopout_pct)
From SASOUT.Attrition07;

run;

/*Reproduce Year 2 Table*/
/*Add Total Values*/

Proc SQL;
Create Table SASOUT.Attrition08A AS 
Select Put(StuLevel,z5.) AS StuLevel
, Yr2_HC, Yr2_TotalCredits, Yr2_AVG_Credits
from SASOUT.Attrition08

Union All

Select 'Total'
, Sum(Yr2_HC), Sum(Yr2_TotalCredits), Sum(Yr2_TotalCredits)/SUM(Yr2_HC)
From SASOUT.Attrition08;

run;


Proc Format;
Value Sectorl 
		1 = "2-year"
		2 = "4-year";

Value $StulevelL 
		'00001' = '1'
		'00002' = '2'
		'00003' = '3'
		'00004' = '4'
		'00005' = '5'
		'00006' = '6'
		'00007' = '7'
		'00008' = '8'
		'00009' = '9'
		'00010' = '10'
		'Total' = 'Total';
run;

proc Print Data=SASOUT.Attrition18 NOOBS SPLIT='*'; 
Label AttritionCost='Attrition Cost' TotalCost='Total Cost' 
AttritionCost_pct = 'Attrition Cost as a %**of Total Cost **(Attrition Rate)';
Format Stulevel $StuLevelL.;
title 'Attrition Cost';
footnote1 'Notes: 1) Attrition cost is the proportion of annual higher education expenditures that go to pay for the instruction of students who never graduate.';
footnote2 '2) Student Level is determined by the number of years passed since the initial year of enrollment.';
footnote3 '3) Attrition rate: % of students who will withdraw from college without a degree.'; 

run;

proc Print Data=SASOUT.Attrition07A NOOBS SPLIT='*'; 
Var StuLevel Retention_pct Grad_pct Stopout_pct;
Label StuLevel = 'Student Level' Retention_pct = 'Retention Rate' 
Grad_pct = 'Graduation Rate' Stopout_Pct = 'Adjustment Rate';
Format Grad_pct percent5.1 Retention_pct percent5.1 Stopout_pct percent5.1 Stulevel $StuLevelL.;
title1 'Retention and Graduation Summary';
footnote1 'Retention Rate: % of students in Year 1 file who returned in the following academic year.';
footnote2 'Graduation Rate: % of students in Year 1 file who graduated by the end of the same academic year.';
run;

proc Print Data=SASOUT.Attrition07A NOOBS SPLIT='*'; 
Var StuLevel Base_HC TotalCredits AVG_Credit;
Label StuLevel = 'Student Level' Base_HC = 'Enrollment' TotalCredits = 'Total Attempted Credits' 
AVG_Credit = 'Average Attempted Credits';
Format Stulevel $StuLevelL.;
title1 'Enrollment Summary - Year 1 Students';
footnote;
run;

proc Print Data=SASOUT.Attrition08A NOOBS SPLIT='*'; 
Var StuLevel Yr2_HC Yr2_TotalCredits Yr2_AVG_Credits;
Label StuLevel = 'Student Level' Yr2_HC = 'Enrollment' Yr2_TotalCredits = 'Total Attempted Credits' 
Yr2_AVG_Credits = 'Average Attempted Credits';
Format Stulevel $StuLevelL.;
Title1 'Enrollment Summary - Year 2 Students';
footnote;
run;


/*Delete tables*/

Proc SQL;
Drop table SASOUT.MyData1;
Drop table SASOUT.MyData2;
Drop table SASOUT.MyData3;
Drop table SASOUT.Attrition01;
Drop table SASOUT.Attrition02;
Drop table SASOUT.Attrition03;
Drop table SASOUT.Attrition03A;
Drop table SASOUT.Attrition03B;
Drop table SASOUT.Attrition04;
Drop table SASOUT.Attrition05;
Drop table SASOUT.Attrition06;
Drop table SASOUT.Attrition07;
Drop table SASOUT.Attrition07A;
Drop table SASOUT.Attrition08;
Drop table SASOUT.Attrition08A;
Drop table SASOUT.Attrition09;
Drop table SASOUT.Attrition10;
Drop table SASOUT.Attrition11;
Drop table SASOUT.Attrition12;
Drop table SASOUT.Attrition13;
Drop table SASOUT.Attrition14;
Drop table SASOUT.Attrition15;
Drop table SASOUT.Attrition16;
Drop table SASOUT.Attrition17;
Drop table SASOUT.Attrition18;
Drop table SASOUT.Fiscaldata;
run;