* H:\sas\oracle\sil\getarscrs.sas ;
* PN 2004/ ;
* use instructor and crstape summary extracts to create ars crsiput file;
* when done, this file is ready to load to SQL server Course database table;
* job order:  xlistcrns, getSILdata, getcrstape, then getarscrs; 
/* NOTE: this file is not equivalent to the old ARS crsiput files because it lacks 
   SOF,paying dept, credited dept.  SOF is stored in SQL table silsof, credit dept
   is stored in SQL table silxlist.  Also, we lack info on instructor rank from ARS.
   Add these fields to this file before putting out to history*/

options nocenter errors=1 noxwait xsync ls=80 obs=max compress=no;

* location and name of output/input data files;
filename CRNsum  ("h:\data\sil\yr200506\fa05\CRNsum000.txt" )  lrecl=142; * getcrstape.sas;
filename session  "h:\data\sil\yr200506\session000.txt" lrecl=137; * getSILdata.sas; 
filename instr    "h:\data\sil\yr200506\instr000.txt"   lrecl=137; * getSILdata.sas;
filename sil      "h:\data\sil\yr200506\sil.txt" lrecl=325; * new ; *do not change location of file;
filename badcrns  "h:\data\sil\yr200506\badcrns.txt" lrecl=50 ; * new, hold list of bad crns;
filename missingI "h:\data\sil\yr200506\missinginstr.txt" lrecl=137 ; * get a file of missing instr for data entry ; 


* read in parms with current academic year, final pay period info ;
  * all hardcoded data should be here;
data _NULL_;
call symput('facheck',"'120058%'") ;   *  term to select ;
call symput('spcheck',"'120061%'") ;   *  term to select ;
call symput('sucheck',"'120065%'") ;   *  term to select ;
call symput('GIScheck',"'120067%'") ;   *  term to select ;
call symput('partofterm',"'  '") ;       *  part of term to select ;
*call symput('mappath',"'\\irt\IR\UIUC\SYS\sil\fa04\'"); * path to files ;
call symput('mappath',"'h:\data\sil\yr200506\'"); * path to files ;  
call symput('fastart',"20050824");  * first day of class ;  
call symput('faend',"20051209");   * last day of instrcution ;   
call symput('spstart',"20060117");   
call symput('spend',"20060503");   
call symput('sustart',"20060515");   
call symput('suend',"20060803");     ** final exam end summer 2 ;
call symput('GISstart',"20050701");   
call symput('GISend',"20060630");   
call symput('curryr',"'2005-06'");  * ac year for this year's records ;
run;


proc sql;
drop view session;
drop view instr;
drop view meeting;


data session;
infile session;
input @01 crs_id               $char07. 
    @08 ctlsubject           $char04.
    @12 ctl_course           $char03.
    @15 ctl_CRN              $char05.  
	@20 section              $char03.
    @23 session              $char02.
	@25 sect_sched_type      $char03.
    @28 mtg_sched_type       $char03.   /* not PKG     */ 
    @31 INSTRN_METHOD_CD     $char04. 
    @35 graded               $char01. 
    @36 package              1.  
    @37 PART_OF_TERM_CD      $char06.  
    @43 PART_OF_TERM_DESC    $char10.
    @53 CHOURS               5.2       /* session contact hours adjusted for weeks */
	@58 sect_status_cd       $char01.  /*a=active,c=cancelled,d=drop,i=inactive,p=pending*/
    @60 BLDG_CD              $char06.  /* first meeting of the week*/
    @66	bldg_desc            $char15.  /* could be 19*/
    @81 SECT_ROOM_NBR        $char10. 
    @91 DOW                  $char06.  
    @97 SECT_BGN_TIME        $char04.
   @101 SECT_END_TIME        $char04.
   @105 weeks                2. 
   @107 sectstc              $char10.	
   @107 sectstart            yymmdd10.
   @117 sectend              yymmdd10. 
   @127 origchours           5.2   /* i hope this will remain null if no chs submitted*/
   @132 TERM_CD              $char06.  ;

   * extramural courses are in the extramural part of term or
    have incstructional method code like XM%;
* community enrollment students are enrolled in and counted in on-campus
    courses, but can be distinguished by their program codes and college of LN;
termtype='TR';
if part_of_term_cd=:'X' or instrn_method_cd=:'X' then termtype='EX';
if substr(term_cd,6,1)='7' then termtype='CC';  * guided indiv study, corresp crse;
if substr(term_cd,6,1)='8' then do;
   if sectstart=. then sectstart=INPUT(PUT(&fastart,Z8.),YYMMDD8.);
   if sectend=. then sectend=INPUT(PUT(&faend,Z8.),YYMMDD8.);
   if sectstc=:'      ' then do;
      sectstart=INPUT(PUT(&fastart,Z8.),YYMMDD8.);
      sectend=INPUT(PUT(&faend,Z8.),YYMMDD8.);
      weeks=15;
   end;
end;
if substr(term_cd,6,1)='1' then do;
   if sectstart=. then sectstart=INPUT(PUT(&spstart,Z8.),YYMMDD8.);
   if sectend=. then sectend=INPUT(PUT(&spend,Z8.),YYMMDD8.);
   if sectstc=:'      ' then do;
      sectstart=INPUT(PUT(&spstart,Z8.),YYMMDD8.);
      sectend=INPUT(PUT(&spend,Z8.),YYMMDD8.);
      weeks=15;
   end;
end;
if substr(term_cd,6,1)='5' then do;
   if sectstart=. then sectstart=INPUT(PUT(&sustart,Z8.),YYMMDD8.);
   if sectend=. then sectend=INPUT(PUT(&suend,Z8.),YYMMDD8.);
   if sectstc=:'      ' then do;
      sectstart=INPUT(PUT(&sustart,Z8.),YYMMDD8.);
      sectend=INPUT(PUT(&suend,Z8.),YYMMDD8.);
      if part_of_term_cd=:'S2 ' then weeks=8;  * S1=summer 1, S2A=first half summer 2;
	    else if part_of_term_cd=:'L1' or  part_of_term_cd=:'L2' then weeks=5;
          else if part_of_term_cd=:'LF'  then weeks=10;
       else  weeks=4;   * S1=summer 1, S2A=first half s2, S2B=second half summer 2 ;
   end;
end;
if substr(term_cd,6,1)='7' then do;
   if sectstart=. then sectstart=INPUT(PUT(&GISstart,Z8.),YYMMDD8.);
   if sectend=. then sectend=INPUT(PUT(&GISend,Z8.),YYMMDD8.);
   if sectstc=:'      ' then do;
      sectstart=INPUT(PUT(&GISstart,Z8.),YYMMDD8.);
      sectend=INPUT(PUT(&GISend,Z8.),YYMMDD8.);
      weeks=15;  * approx;
   end;
end;
* create a variable for identifying concurrent sections;
timeplace=term_cd||' '||sect_bgn_time||'-'||sect_end_time||' ' ||dow||' '||sectstc
       ||' '||sect_room_nbr||' '||bldg_desc;


if weeks=. then weeks=15;
if not(sect_sched_type=:'PKG') then mtg_sched_type=sect_sched_type; 
** take sched type from section base unless it is packaged;
run;

proc sort nodupkey data=session;
by term_cd ctl_crn session mtg_sched_type;

data instr;
infile instr;
input @01 uin           $char09.
    @10 ssn             $char09. 
    @19 edw_pers_id     7. 
    @26 lname           $char15.
    @41 fname           $char15.
    @56 mname           $char10.
    @66 crs_id          $char07.
    @73 ctl_CRN         $char05. 
	@78 ctlsubject      $char04.
    @82 ctlnbr          $char03. 
    @85 sect_nbr        $char03.
	@88 session         $char02.
    @90 adjpct          6.2           /*0.00 to 100.00*/
    @96 priminstr       $char01.   /* Y or N */
    @97 ASSIGN_TYPE_CD  $char04.
   @101 sess_tot_pct    6.2
   @107 eclass         $char02.
   @109 fac_count       2.
   @111 offload         $char01.
   @112 termtype        $char02.
   @114 part_of_term_cd $char06.
   @120 term_cd         $char06.  
   @126 tenure          $char12.  ;
instr_name=substr(trim(lname)||" "||trim(fname)||" "||trim(mname)||"                    ",1,20);
run ;
*data test1;
*set session;
*if (ctl_crn='40264' or ctl_crn='30013') and term_cd='120051';
*run;
*proc print;
*title1 'Session';
*run;


*proc tabulate data=instr;
*class term_cd;
*table term_cd,n;
*run;
proc sort;
by term_cd ctl_crn session ;

  ** add instructor info to session, many to many; 
proc sql;
create table instsect as 
select s.*,i.uin, i.ssn,i.instr_name,i.adjpct,i.priminstr,i.assign_type_cd,
 i.sess_tot_pct,i.fac_count,i.offload,i.eclass,i.tenure from session s 
 left outer join instr i on (s.ctl_crn=i.ctl_crn and s.session=i.session and s.term_cd=i.term_cd) ;

data instsect1;
set instsect;
if instr_name=:'    ' then do;
  uin='999999999';
  ssn='999999999';
  instr_name='No instructor given';
  adjpct=100;
  priminstr='Y';
  assign_type_cd='    ';
  sess_tot_pct=0;
  fac_count=1;
  offload='N';
end; 

run;

proc sort ;
by term_cd ctl_crn mtg_sched_type priminstr;
run;

data CRNsum;
infile CRNsum ;
input  @001 ctl_crn  $char05.   
       @006 mtg_sched_type   $char03. 
       @010 ius1    6.1   /* freshmen: class 1 */
       @017 enr1    6.1
       @024 ius2    6.1   /* soph: class 2 */
       @031 enr2    6.1
       @038 ius3    6.1   /* junior: class 3 */
       @045 enr3    6.1
       @052 ius4    6.1   /* senior: class 4 */
       @059 enr4    6.1
       @066 ius5    6.1   /* ug ndeg: class 5 (derived from NDE in pgm*/
       @073 enr5    6.1
       @080 ius6    6.1   /* gr ndeg: class 6 (derived from NDE in pgm) */
       @087 enr6    6.1
       @094 ius7    6.1   /* g1: class 7 (master)*/
       @101 enr7    6.1
       @108 ius8    6.1   /* g2:class 8 (PHD,AMUSD,EDD,AUD)*/
       @115 enr8    6.1  
       @122 ius9    6.1   /* profnl: class 9 */
       @129 enr9    6.1  
       @136 term_cd $char06.    ;
sumius=ius1+ius2+ius3+ius4+ius5+ius6+ius7+ius8+ius9;
sumenr=enr1+enr2+enr3+enr4+enr5+enr6+enr7+enr8+enr9;
run;

proc tabulate data=crnsum missing;
class term_cd;
var sumius sumenr;
table all term_cd,n sum=''*(sumius='IUS' sumenr='ENR');
title1 'IUs and Enrollments in CRNSUM files ';;
title2 'Should match the IUs & Enr from the course tape create and the TNTcreate';
title3 '';
run;
proc sort nodupkey data=crnsum;
by term_cd ctl_crn mtg_sched_type ;

data instsectcnt badsects (keep=ctl_crn term_cd mtg_sched_type sumius sumenr) ;
merge instsect1(in=a) crnsum(in=b);
by term_cd ctl_crn mtg_sched_type;
if a and b then output instsectcnt;
if b and not a then output badsects;
run;


proc tabulate data=badsects missing;
class term_cd ;
var sumius sumenr;
table all term_cd ,
(sumius='IUs' sumenr='Enr')*sum=''/rts=34;
title1 'IUs and Enr from CRNs with no matching meeting record';
title2 'Run checkbadcrns.sas';
title3 '';
run;

data badsects;
set badsects;
file badcrns;
put @1 term_cd $char06. @7 ctl_crn 7. @14 mtg_sched_type $char03. 
   @17 sumius 6. @23 sumenr 6.;
run;



proc tabulate data=instsectcnt;
class term_cd;
table term_cd,n='Sections'*f=comma8.0 ;
title1 'Number of sections by term ';
title2 'These will be loaded to SIL ';
run;

** special fix for FY05 for CEE and for all the missing CHs;
data instsectcnt;
set instsectcnt;
if chours=0 and  (mtg_sched_type='IND' or mtg_sched_type='CNF') then do;
   if ctlsubject=:'CEE ' then do;
       if ctl_course='599'  then chours=2.5 * sumenr;
        else  chours=sumenr;
   end;
   if not(ctlsubject='CEE ') then do;  * these numbers were averages for FY04;
     fixedchrs='Y';    * this will cause a 2 in nochrsflag in SIL ; 
     if ctl_course=:'1' then chours=2.0;
	   else if ctl_course=:'2' then chours=2.5;
         else if ctl_course=:'3' then chours=2.7;
		   else  chours=4.2;
   end;

end;


run;



                      ** now we will prorate ius and enrollments amoung instructors for a section;
                      ** last instructor (primary) gets the difference ;  
                      ** note: if no contact hours have been submitted, all ius and enr ;
                      ** will be posted to the primary instructor ;

data penultsil;
format ch chleft 5.2;
set instsectcnt;
by term_cd ctl_crn mtg_sched_type;
array ius{9} ius1-ius9;
array enr{9} enr1-enr9;
array iusleft{9} iusleft1-iusleft9;
array enrleft{9} enrleft1-enrleft9;
retain iusleft1-iusleft9 enrleft1-enrleft9 chleft;
if first.mtg_sched_type then do;
  chleft=chours;  
  do i=1 to 9;
	iusleft{i}=ius{i};
	enrleft{i}=enr{i};
  end;
end;
if last.mtg_sched_type then do;  * this should be primary instr; 
  ch=chleft;                  ** amounts will never be less than 0;
  do i=1 to 9;
     ius{i}=iusleft{i}; 
     enr{i}=enrleft{i};
  end;
  enrtot=sum(enr1-enr9);  * compute totals for this instructor  after proration;
  iustot=sum(ius1-ius9);
end;
* prorate the contact hours, ius and enrollments to each instructor ;
if fac_count>1 and not(last.mtg_sched_type) then do ;
  ch=min(chleft,int(chours*adjpct/10)/10);   ** computed adjusted contact hours ;
  chleft=chleft-ch;
  do i=1 to 9 ;
   ius{i}=min(iusleft{i},int(ius{i}*adjpct/10)/10);   * adjust ius/enr for percent under 100%, truncate to 2 decimals ;
   enr{i}=min(enrleft{i},int(enr{i}*adjpct/10)/10);   * do not go below ius or enrs left ;
   iusleft{i}=iusleft{i}-ius{i};    * get running total of ius/enrs left for each student group for this section; 
   enrleft{i}=enrleft{i}-enr{i};    * but do not let it fall below 0; 
 end;
end ;
run;

*data test4;
*set penultsil;
*if ctl_crn=39121;
*proc print;
*run;

* identify concurrent sections for one instructor;
* we will need to reduce contact hours if same instructor is in 
  two concurrent sections at same time & place;


data chkconcur;
set penultsil;
if uin='999999999' then delete;
if sect_sched_type ='IND' then delete;
if dow='      ' then delete;
if sect_status_cd='A';
run;

proc sort data=chkconcur;
by uin timeplace;

data idconcur(keep=uin timeplace n);
set chkconcur;
by uin timeplace;
retain n;
if first.timeplace then n=0;
n+1;
if last.timeplace  ;
run;


proc sort data=penultsil;
by uin timeplace;

data addconcur;
merge penultsil(in=a) idconcur(in=b);
by uin timeplace;
if a;
if not b then n=1;  * n=number of concurrent sections for an instructor;
run;

options missing=' ';

data finalsil;
set addconcur;
if mtg_sched_type='IND' then ic='I';
else ic='C';
enrtot=enr1+enr2+enr3+enr4+enr5+enr6+enr7+enr8+enr9;  * compute totals for this instructor  after proration;
iustot=ius1+ius2+ius3+ius4+ius5+ius6+ius7+ius8+ius9;
sess_chrs=0;
cr_campus='1' ; * always urbana ;
ctl_campus='1';
year=substr(term_cd,2,4);
month='0'||substr(term_cd,6,1); 
if graded='Y' then noncrd='  ';
else noncrd='NC'; 
if priminstr='Y' then priminstr='P';
else priminstr=' ';
nochrsflag=0;
if origchours=. then nochrsflag=1;
if fixedchrs='Y' then nochrsflag=2;
if ch=. then ch=0;
if chours=0 then chours=0;
if chours>99.99 then do;
  put 'Chours > 99  ' chours ctlsubject ctl_crn term_cd ;
  chours=99.99;
end;
if ch>99.99 then do;
  put 'Ch > 99      '  chours ctlsubject ctl_crn term_cd  instr_name;
  ch=99.99;
end;

adj_ch=ch;     * adjusted contact hours --use for TNT process and crshist; 
               * for SIL, use plain contact hours;
if n>1 then do; 
  adj_ch=ch/n;
  concurflag='C';  * adjust instructor contact hours for concurrent sections;
end;
else  concurflag=' ';  
if weeks<15 then adj_ch=adj_ch*weeks/15;  *adjust contact hours for lenght of crs;

file sil;
put @1 _N_      5.
    @6 &curryr 
    @13 year               $Char04.
    @17 month              $char04.    
    @19 uin                $char09.  
    @28 ssn                $char09.  
	@37 termtype           $char02.  
    @39 ctl_crn            5.
    @44 ctlsubject         $char04.
    @48 ctl_course         $char03.
	@51 crs_id             $char07.
	@58 ctl_course         $char01.   /* course level */
	@59 mtg_sched_type     $char03.
	@62 section            $char03.
    @65 instr_name         $char20.  /**/
    @85 offload            $char01.  /**/
    @86 fac_count            2.      /**/
	@89 nochrsflag         1.        /* 1= no contact hours submitted, 2=none submitted, chrs were imputed */
    @90 eclass             $char02.  /* eclass, if an employee, blank otherwise*/
	@92 tenure             $char12.  /* Tenured, Tenure Track, Nontenured */
   @105 enr1 6.1 @111  enr2 6.1 @117 enr3 6.1 @123 enr4 6.1 @129 enr5   6.1 
   @135 enr6 6.1 @141 enr7 6.1  @147 enr8 6.1 @153 enr9 6.1 @159 enrtot 6.1
   @165 ius1 6.1 @171 ius2 6.1  @177 ius3 6.1 @183 ius4 6.1 @189 ius5   6.1 
   @195 ius6 6.1 @201 ius7 6.1  @207 ius8 6.1 @213 ius9 6.1 @219 iustot 6.1
   @225 ic                 $char01.
   @226 weeks              2.       /* first meeting */
   @228 ch                 5.2      /*prorated contact hours, this instructor. 0 if none
                                     not adjusted for concurrent sections or short term */
   @233 chours             5.2      /*contact hours all instr this session, 0 if none*/ 
   @238 SECT_BGN_TIME     $char04.   
   @242 SECT_END_TIME     $char04.   
   @246 dow                $char06.  
   @252 sectstart          yymmdd10.  /* this formast is needed */
   @262 sectend            yymmdd10.   
   @272 sect_room_nbr      $char10.    
   @282 BLDG_CD            $char06.    
   @288 BLDG_desc          $char15.
   @303 sect_status_cd     $char01.   /*a=active,c=cancelled,d=drop,i=inactive,p=pending*/

   @305 noncrd             $char02.  /* NC or blank*/
   @307 priminstr          $char01.  /* P or blank*/
   @308 session            $char02.
   @310 part_of_term_cd    $char04. 
   @314 term_cd            $char06. 
   @320 concurflag         $char01.   /* C= concurrent sections for this instructor 
            contact hours have been adjusted  */
   @321 adj_ch             5.2   /* contact hours adjusted for concurrent courses 
                                 for this instructor and for number of weeks */ ;

run;

data missinginstr;
set finalsil;
if uin='999999999';
adjpct=100;
sess_tot_pct=100;
assign_type_cd='    ';
file missingi;
put @01 'uin------'
    @26 'lname----------'   
    @41 'fname----------'
    @56 'mname-----'
    @66 crs_id          $char07.                           
    @73 ctl_CRN         $char05.                           
    @78 ctlsubject      $char04.                       
    @82 ctl_course      $char03.                           
    @85 section         $char03.                           
    @88 session         $char02.                       
    @90 adjpct          6.2           /*0.00 to 100.00*/   
    @96 'Y'                           /* primary Y or N */            
    @97 '    '                        /*ASSIGN_TYPE_CD */
   @101 sess_tot_pct    6.2                                
   @107 '  '                       /* eclass */                        
   @109 ' 1'                       /* fac count */
   @111 'N'                        /*offload*/
   @112 termtype        $char02.                           
   @114 part_of_term_cd $char06.                           
   @120 term_cd         $char06.                           
   @126 'tenure------'   ;           
run;   
   
                                           
data _NULL_;                            
path=&mappath ;                         
filenm='sil.txt';                       
versions=4;                             
%include 'h:\sas\utility\gdgupdt.inc'  ;
run;