* H:\sas\sil\FAgetcrstape.sas ;
* initially FAfinalgetcrstape.sas -- adds second half-term enrollments ;
* original FAgetcrstape.sas renamed to FAgetcrstape.save.sas;
* PN 2004/ ;
* extract student enrollment information and create a final course tape ;
* assumes that 10-day course tape has already been created from 10-day census ;
* when the drop date/extramural census file is ready, will use this for final coursetape;
* job order:  xlistcrns, getSILdata, getcrstape, finalgetcrstape, then getarscrs;

* note: per prior agreement, PHYS 100 is included as 2nd 8-wk class although it's partofterm=1 ;
* 5/4 modified to get 10 day and drop day data from one SQL query. ; 
* 2/23 modified to get calculated class codes from later snapshot (one-time-only fix);
* 3/17 modified to impute ius for crse numbers < 100;
* 3/17 modified to fix bad class codes /and add enrollments for MBA 500, MSP 600,601/ ;
* note: missing MBA/MSP enrollments added to original crstape once with FAgetcrstape.missingcrses;
*       they're read in here from crstape10day.txt;
*       they don't need to be added again here because they're all full term;
* 10/6: took MSP courses out again to match enrollment statistics logic ;
*       MSP courses are place-holders, not really on-campus or credit-bearing ;
*       so Medical Scholars taking only MSP courses will be removed ;
*       Medical Scholars taking other on-campus courses will remain on course tape ;

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


* location and name of output/input data files;
filename crnsmtst "h:\data\sil\yr200506\fa05\crnsum.txt" lrecl=142;  
filename subjects "h:\data\sil\yr200506\fa05\subjects.txt"  lrecl=100;
filename sessions "h:\data\sil\YR200506\session000.txt"  lrecl=137;
filename pkgmap   "h:\data\sil\YR200506\pkgmap000.txt"   lrecl=19;

run;

* read in parms with current academic year, final pay period info ;
  * all hardcoded data should be here;
data _NULL_;
call symput('checkterm',"'120058%'") ;   *  term to select ;
call symput('mappath',"'h:\data\sil\yr200506\fa05\'") ;
run;

  * get xlist info from SQL server ;
  * this has one record per crn per sched type ;
proc sql;
%include  'h:\carol\personal\connections\SQL-dmiathenaCRS.inc';
*connect to SQLSERVR as crs (user=xxxxxxx pass=xxxxxxx server='DMIATHENA' database='Course');
create view xlist as select * from connection to crs 
(select * from silxlist where term like &checkterm
order by crn
);
 
disconnect from crs;
quit;


data xlist1;
set xlist;
if not(idtype='PKG') then mtg_sched_type=idtype; ** take section base sch type unless package;
  else mtg_sched_type=pkg_mtg_sched_type;   * pkg_mtg_sched_type is from meeting, idtype is from sect base;
drop pkg_mtg_sched_type;
run;

  ** create one file to convert crn to controlling dept crn & info ;
proc sort nodupkey out=ctlcrns (keep=subject crn ctl_crn ctl_subject ctl_crse);
by crn ; 

proc sort ;
by ctl_subject;
run;

* also create a file to map Ctl_crn & schedule type to credited crn, crsubject, crcrse; 
data creditcrns (keep=ctl_crn mtg_sched_type cr_subject cr_crse cr_crn);
set xlist1;
if credited;
cr_crn=crn;
cr_subject=subject;
cr_crse=crse;
run;

proc sort nodupkey;
by ctl_crn mtg_sched_type;

proc sort;
by cr_subject;
run;

   ** now, get coll and dept for all subjects. this is in subject order, no need to sort; 
   
data subjctl(keep=ctl_subject ctl_coll ctl_dept)
      subjcr(keep=cr_subject cr_coll cr_dept)
     subject(keep=subject crs_coll crs_dept);
infile subjects;
input @1 ctl_subject $char04.  @1 cr_subject $char04.   @1 subject $char04.
     @06 ctl_coll    $char02.  @6 cr_coll    $char02.   @06 crs_coll $char02.
     @10 ctl_dept    $char03. @10 cr_dept    $char03.   @10 crs_dept $char03. ;  * note: take only 3digit dept ;
run;

data ctlcrns2;
merge ctlcrns(in=a) subjctl(in=b);
by ctl_subject;
if a;
run;

proc sort data=ctlcrns2;
by subject crn ;
run;

data creditcrns2;
merge creditcrns(in=a) subjcr(in=b);
by cr_subject;
if a;
run;

proc sort data=creditcrns2;
by ctl_crn mtg_sched_type ;
run;


** read in the pkgmap file created by GETSILDATA.sas;

data pkgmap;
infile pkgmap;
input @1 ctl_crn  5.   /* this is a controlling crn, perhaps not the first for the sch type */
      @6 mtg_sched_type $char03. 
      @9 first_ctl_crn 5. /* this is the first crn for all pkgs with this sched type*/
      @14 term         $char06.    ;
* note: ctl_crn may = crn ;
if term =: &checkterm;
run;

proc sort data=pkgmap;
by ctl_crn ;
run;
*** get section contact hours from session file produced by getSILdata.sas;

data sessions;   
infile sessions ;   * one record per session per controlling dept CRN ;
input 
    @01 crs_id               $char07. 
    @15 ctl_CRN              5.  /* controlling dept CRN for the session */
    @28 mtg_sched_type       $char03.  /* populated only for pkgs*/
    @53 sessionContacthrs        5.2 
    @132 TERM_CD              $char06. ;
if TERM_CD =: &checkterm;
run;


** first, combine multiple sessions for one sched type, adding up the contact hours for the sessions ;
proc sort data=sessions;
by ctl_crn mtg_sched_type ;

data sessions2;   ** we need to do this to use only the contact hrs on the ctl crn rec;
set sessions;
by ctl_crn mtg_sched_type;
retain sectcontacthrs;
if first.mtg_sched_type then sectcontacthrs=0;
sectcontacthrs+sessionContacthrs;
if last.mtg_sched_type;
run;
           * must be careful to mark exactly one sched type per crn as creditbearing 
             or we will multiply the iUs;
data sessions2 (keep= ctl_crn mtg_sched_type sectcontacthrs multiplier);
set sessions2;
by ctl_crn;
if first.ctl_crn then multiplier=1;
else multiplier=0;
run;


/* Sign on to IRPROD not needed if autodone*/

*signon irt.unxspawn;

rsubmit irtprod.unxspawn;

proc upload data=creditcrns2 out=creditcrns2;
run;
proc upload data=ctlcrns2 out=ctlcrns2;
run;

proc upload data=pkgmap out=pkgmap;
run;
proc upload data=sessions2 out=sessions2;
run;
proc upload data=subject out=subject;
run;


filename crstape "/IR/UIUC/SYS/sil/fa05/crstape000" lrecl=300; 


data _NULL_;
call symput('checkterm',"'120058%'") ;   *  term to select ;
call symput('snapshot1',"113") ;       *  snapshot to select :  10 day;
call symput('snapshot2',"99999") ;       *  snapshot to select :  drop date, use for XM;



proc format;  * need this to encode schedtype ;
value $stype 
'Bus Transportation   '='BUS'
'Clinical Practice    '='CLP'
'Conference           '='CNF'
'Cooperative Education'='COP'
'Discussion/Recitation'='DIS'
'Flight               '='FL'
'Independent Study    '='IND'
'Laboratory           '='LAB'
'Laboratory-Discussion'='LBD'
'Lecture              '='LEC'
'Lecture-Discussion   '='LCD'
'Online               '='ONL'
'Packaged Section     '='PKG'
'Practice             '='PR'
'Quiz                 '='Q'
'Studio               '='ST'
'Study Abroad         '='STA' ;
run;


** get 10-day student enrollment info ;
PROC sql ;
%include  '/IR/UIUC/home/livngstn/connections/edwdirect.inc';
* has one statement;
*connect to oracle as edw (user=livngstn orapw=xxxxxx path='dsprod01');

create view sumius as select * from connection to EDW
(select sum(e.crs_credit_hour) as secthrs    /* blank in snapshots 6 and 7 */
 from EDW.T_RS_STUDENT_CRS_INFO e
  left outer join EDW.T_RS_STUDENT st on (e.edw_pers_id=st.edw_pers_id 
            and st.REG_SNAPSHOT_KEY =&snapshot1
            and st.student_status_cd like 'A%')
 where e.REG_SNAPSHOT_KEY=&snapshot1 and e.campus_cd='100'
   and e.student_crs_reg_cur_info_ind='Y'
	and e.crs_reg_status_cd like 'R%' 
   );


create view stuenr as select * from connection to EDW
(select 
e.reg_snapshot_key         as snapshot,
edw_pers_id,
e.crn_credit_hour          as secthrs,     /* this has data */
e.crn_bill_hour,       
&checkterm                 as term,      
st.student_type_cd         as sttype,
st.calc_cls_cd             as stuclass,    /* useless through snapshot 7 */
st.student_level_cd        as stlevel,
st.student_acad_pgm_cd     as program,
st.coll_cd                 as acadcoll,
st.dept_cd                 as dept,
st.student_curr_1_major_cd as major,
st.STUDENT_CURR_1_CONC_1_CD as conc,
st.STUDENT_CURR_1_MAJOR_CIP_CD as majorcip,
st.student_curr_1_deg_cd   as degree,
p.pers_lname               as lname,
p.pers_fname               as fname,
p.pers_mname               as mname,
p.uin,
p.sex_cd                   as sexcode,
p.race_eth_cd              as stuethnic,
p.PERS_CITZN_TYPE_CD       as cit_type,
s.part_of_term_cd          as termtype,
s.sched_type_desc,
s.instrn_method_cd         as imethod,  /* need for some exm crses*/
s.crs_subj_cd              as subject,
s.crs_nbr                  as crse,
s.sect_nbr                 as section,
e.crn                      as crnc,   /* this is character data, we want numeric*/
nvl(s.crs_id,'9999999')    as crs_id,                   /* returns 9999999 if crsid is null */
s.sect_gradable_ind        as graded,  /* Y/N*/
nvl(t.crsius,0)            as crsius                    /* returns 0 if crsius is null */

from EDW.T_RS_STUDENT_CRS_INFO e
inner join EDW.T_RS_SECT_BASE s on (s.REG_SNAPSHOT_KEY=e.REG_SNAPSHOT_KEY  
       and e.crn=s.crn and sect_status_cd='A')
left outer join EDW.T_RS_STUDENT st on (e.edw_pers_id=st.edw_pers_id 
            and st.REG_SNAPSHOT_KEY=e.REG_SNAPSHOT_KEY 
            and st.student_status_cd like 'A%')
left outer join EDW.T_RS_PERS p on (e.edw_pers_id=p.edw_pers_id and p.REG_SNAPSHOT_KEY=e.REG_SNAPSHOT_KEY  )
left outer join (select edw_pers_id,crs_id,sum(crn_credit_hour) as crsius,
             te.REG_SNAPSHOT_KEY
   from EDW.T_RS_STUDENT_CRS_INFO te, EDW.T_RS_SECT_BASE ts
   where te.REG_SNAPSHOT_KEY=ts.REG_SNAPSHOT_KEY 
     and te.campus_cd='100' and ts.campus_cd='100' 
     and te.student_crs_reg_cur_info_ind='Y'  /* D  would be dropped */
	 and te.crs_reg_status_cd like 'R%' /* omits CN DN DC DW WW */
     and (ts.REG_SNAPSHOT_KEY=&snapshot1 or ts.REG_SNAPSHOT_KEY=&snapshot2)
     and te.crn=ts.crn 
   group by edw_pers_id,ts.crs_id,te.REG_SNAPSHOT_KEY) t on (e.edw_pers_id=t.edw_pers_id 
     and s.crs_id=t.crs_id and t.REG_SNAPSHOT_KEY=e.REG_SNAPSHOT_KEY )
where (e.REG_SNAPSHOT_KEY=&snapshot1 or 
      (e.REG_SNAPSHOT_KEY=&snapshot2 and     
         (s.part_of_term_cd='B'         /*second 8 week */
       or s.part_of_term_cd='X' or s.part_of_term_cd=' ' or st.coll_cd='LN' /*extramural    */ 
       or s.crs_id='1005760'         /*physics 100   */
	 /*  or s.crs_id='1008604'*/)))     /* FA04 MBA 500, no crs_id in 10 day file*/
  and e.campus_cd='100'
  and e.student_crs_reg_cur_info_ind='Y' 
  and e.crs_reg_status_cd like 'R%'   /* order by fails due to oracle case insensitive*/
  order by e.edw_pers_id,s.crs_id,upper(s.sect_nbr),
        upper(s.sched_type_desc),e.reg_snapshot_key)
;

drop view speccrs1;      /* need this for MSP courses */                     
create view speccrs1 as select * from connection to edw
(select edw_pers_id,
       /* crn_credit_hour as crdtamt, */ 
	   r.crn             as crn,
       crs_nbr           as crse,
	   crs_subj_cd       as subject
 from EDW.T_RS_STUDENT_crs_info r,
      edw.T_RS_sect_base sb 
 where  r.STUDENT_CRS_REG_CUR_INFO_IND ='Y' 
    and r.crs_reg_status_cd like 'R%'  
	and r.reg_snapshot_key=&snapshot1 
	/* and sb.term_cd='120051' */
    and sb.reg_snapshot_key=&snapshot1  
    and sb.crn=r.crn
    order by edw_pers_id);

disconnect from EDW;
quit;

*proc print data=sumius noobs;
*title1 'Sum of credit hours enrolled in this term';   /* blank for fa04 */
*run;                                                    

data stuenr1;   * get student enrollments by crn ; 
length lname fname mname $ 30. ;
set stuenr;
** fix problems this term ;
run;

proc sort ;
by edw_pers_id;
run;
						/* Need to delete MSP course(s). First, id all non-msp */
data speccrs11 (keep=edw_pers_id);
set speccrs1;
if subject='MSP' then delete;
run;

proc sort nodupkey;  * now, create a list of all pers ids who have non MSP crses;
by edw_pers_id;
run;
              /* this keeps Medical Scholars who have other courses besides MSP */
DATA stuenr10; /* note, it will keep MSP courses for these students */
  merge stuenr1(in=a) speccrs11(in=b);
  by edw_pers_id; 
  if a and b;
run;

proc sort data=stuenr10;
by edw_pers_id crs_id section sched_type_desc;

 * eliminate dups due to second snapshot.  if a person changed sections after 
  10 day in a sec 8 wk or extramural course we'll dbl count - this is rare ;
data stuenr2;
set stuenr10;  
by edw_pers_id crs_id section sched_type_desc;
if snapshot=&snapshot2 and not(first.sched_type_desc) then delete; * all 10day enrolls stay;
run;                        

data stuenrbyCRN;
set stuenr2;
crn=1*crnc; ** convert character crn to number ;
if crs_id=:'9999999' then delete;
if sexcode='N' then sexcode='M';         /* typos on some student genders */
idtype=put(trim(sched_type_desc),$stype.);
dept=substr(dept,2,3) ; * eliminate campus from dept;
imputeflag=0;
if crsius=0 then do ;  * impute hours based on level ;
  imputeflag=1;
  select (substr(crse,1,1));
     when ("0") crsius=1;
     when ("1") crsius=1;
	 when ("2") crsius=2;
	 when ("3") crsius=3;
	 otherwise crsius=4;
  end;
end;
run;

*run;
*proc print;
*run;
*proc tabulate data=test missing;
*class subject crse;
*var crsius;
*table subject*crse,n crsius*sum=''/rts=20;
*run;




* ok, next we will read in student records and add subject dept and college to each record;
* number of student records should remain the same ;
proc sort data=stuenrbyCRN;
by subject;

data stusubj nosubj;
merge stuenrbyCRN(in=a) subject(in=b);
by subject;
if a;
if b then output stusubj;
else output nosubj;
run;

proc sort nodupkey data=nosubj;
by subject;

proc print data=nosubj;
title1 'These subjects were missing from irt...subject.txt';
*var subject;
run;

proc sort data=stusubj;
by subject crn;

* ok, next we will match controlling crn list back to student-course records, adding in
  ctl_crn, ctl_subject, ctl_dpet,ctl_coll to each student rec;  
* number of student records should remain the same ;

data stuall;
merge stusubj(in=a)   ctlcrns2(in=b);
by subject crn ;
if a;
if not b then do ; * not crosslisted, controlling and credited will be the same as offered;
  ctl_crn=crn; 
  ctl_subject=subject;
  ctl_dept=crs_dept;
  ctl_coll=crs_coll; 
end;
run;


proc sort data=stuall;
by ctl_crn;

* above file is one record/student/crn, and each rec has the controlling dept crn added;

** now, we must expand student rec packaged crns to get one rec /sched type;
** and to find the first crn for each of the orig crn-sched type combo.;

proc sql;
drop view expstuallv;
create view expstuallv as 
(Select a.*,b.first_ctl_crn,mtg_sched_type
from stuall a left outer join pkgmap b
on a.ctl_crn=b.ctl_crn);

data expstuall2;
set expstuallv;
if mtg_sched_type='   ' then do;
  mtg_sched_type=idtype;
end;
else do;
  idtype=mtg_sched_type;
  ctl_crn=first_ctl_crn;
end ; 
run;

*data test7;
*set expstuall2;
*if crn=31774 or ctl_crn=31774 ;

*proc print data=test7;
*var crn subject uin idtype mtg_sched_type ctl_crn;
*title1 'one crn to look at ';
*run;

proc sort data=expstuall2;
by ctl_crn mtg_sched_type;


** now match sessions to the student record ;

data stusession;
merge expstuall2(in=a) sessions2(in=b);
by ctl_crn mtg_sched_type;
if a;
if not b then do;
  Sectcontacthrs=0;  
  mtg_sched_type=idtype;
  multiplier=1;   
end; 
** fix student credit hours.  for a pkg, the CRN credit hours will have been applied to
   each sched type, so need to zero out all but  one and mark only one as graded.;
** this is not quite correct, since the entire package was graded, but ok ;
secthrs=secthrs*multiplier; 
if multiplier=0 then graded='N';
run;

   *** now we can add in the credited course information ;
proc sort data=stusession;
by ctl_crn mtg_sched_type;

data stuwithcredited;
merge stusession(in=a) creditcrns2(in=b);
by ctl_crn mtg_sched_type;
if a;
if not b then do;   * course is not crosslisted, it will be credited to offering unit ;
  cr_crse=crse;
  cr_subject=subject; 
  cr_coll = crs_coll;
  cr_dept = crs_dept ;
end;
run;


           * now we must count the final number of sections & total contact hours 
             per student per crsid;
proc sort data=stuwithcredited;
by uin crs_id ;
run;

*data test;
*set stuwithcredited;
*if ctl_crn='39807' or ctl_crn='39808';       /* TSM */
*proc print;
*run;

** get total contact hours for course for each student.compute prorated IUs, and output;
data uincrschrs(keep=uin crs_id crscontacthrs sectcnt);
set stuwithcredited;
by uin crs_id;
retain crscontacthrs sectcnt;
if first.crs_id then do;
  crscontacthrs=0;
  sectcnt=0 ;
end;
crscontacthrs+sectcontacthrs;
sectcnt+1;
if last.crs_id;
run;

data final;
merge stuwithcredited(in=a) uincrschrs(in=b);
by uin crs_id;
if a;
if not b then do;
  crscontacthrs=0;
  sectcnt=1;
end;
run;

data final1;
set final;
by uin crs_id;
retain crsused;
if first.crs_id then crsused=0;
if last.crs_id then sectius=max(0,crsius-crsused);
else do;
 if crscontacthrs>0 then sectius=round(crsius*sectcontacthrs/crscontacthrs,0.1); 
  else sectius=round(crsius/sectcnt,0.1);    ** if no crschrs, prorate evenly over all sections ;
end;
crsused+sectius;

stname=trim(lname)||" "||trim(fname)||" "||trim(mname);
part_of_term_cd=termtype;
if termtype=' '  or termtype=:'X' or imethod=:'X' then termtype='X'; * some xm  have blank termtype;
else if termtype=' ' and substr(&checkterm,6,1)='7' then termtype='Y';
else termtype=' ';  * regular on-campus term ; 
activity='01' ; *default, mark later;
if termtype='X' then activity='48' ; *XM offload default until data collected on instr;
if termtype='Y' then activity='49' ; * cc offload default until data collected on instr;
fund='1' ; * default, mark later; 
if program in('10KS3924MBA','10KS3924MBA2','10KS3925MS','10KS3926MS','10KS3927MS','10KS3928MS','10KS4063MS','11KS3924MBAX','10KS3924MBAX') 
 then costrec='Y'; * mark cost recovery students ;
else costrec='N' ; 
if acadcoll='  ' and not(program=:'10KS') then acadcoll=substr(program,3,2);
if program=:'10KN0092NONE' then dept='335'; * problem with sp05; 
if program=:'10LC0480DVM' and stuclass=:' ' then stuclass='Y1';  * missing vet class codes ;
run;


data test;
set final1;
if stuclass='  ' or program=:'    ' or acadcoll=:'  ' or dept=:'   ' ;      /* see if anyone's still left blank */
run;

proc tabulate data=test missing;
title1 'missing student class, program, college, dept ';
class  stuclass program acadcoll dept ;
table acadcoll*dept*program,stuclass*n=''/rts=50;
run;
                /* get class levels (still need to break up grads) */
data final2;    /* no student groups yet -- do that after final merge */
set final1;
if stuclass>='1' and stuclass <='5' then stclass=' '||stuclass;
                /* Banner has class code 5 but doesn't use it */
if stlevel='1U' and substr(program,9,3)='NDE' then stclass=' 5';      /* so it's calculated here */
      
if stlevel='1G' then do;
	if substr(program,9,3)='NDE' then stclass=' 6';
    else if degree=:'PHD' or degree=:'EDD' or degree='AMUSD' or degree='AUD' or degree='JSD' 
      then stclass=' 8';
    else stclass=' 7';
	end;
if stlevel='1L' or stlevel='1P' or stlevel='1V' then    /* undeclared profs are 1P--shouldn't be any by census */
      stclass=stuclass;          /* law and vet use Y1 . .. Y4 */
if stlevel='1U' then select (stclass);
  when (' 1') coststlvl='LD';
  when (' 2') coststlvl='LD';
  when (' 3') coststlvl='UD';
  when (' 4') coststlvl='UD';
  when (' 5') coststlvl='UD';
 otherwise  coststlvl='LD';
end;
if stlevel='1G' or stlevel='1P' or stlevel='1L' or stlevel='1V' then select (stclass);
  when (' 6') coststlvl='G1';  * non-degree grad ;
  when (' 7') coststlvl='G1';  * master's ; 
  when (' 8') coststlvl='G2';  * doctoral ;
  otherwise coststlvl='G1';  * includes professionals ;
end;

run;

proc sort;
by uin crs_id;

* secthrs=credit hours this section crsius=sum of hours for crs for stud, imput added in;
* crscontacthrs=total contact hours all sections this crs;
* sectcnt=number of sections this course ;
* sectcontacthrs=contact hours this section ;

*** now students have all been recalculated as of snapshot 7 ;
                       
data final3;
set final2;
if cr_crn=. then cr_crn=crn;
if ctl_crn=. then ctl_crn=crn;
* stgrp is needed for placement of output for crnsum ; 
if stlevel='1L' or stlevel='1P' or stlevel='1V' then stgrp=9;   /* undeclared profs are 1P--shouldn't be any by census */
   else stgrp=stclass*1;
if crs_dept='   ' and subject='MSP' then crs_dept='761';   /* missing from EDW for some reason */
if cr_dept='   ' and cr_subject='MSP' then cr_dept='761';

year=1*substr(&checkterm,4,2);  * compute academic year ;
if substr(&checkterm,6,1)='8' then acyear=substr(&checkterm,4,2)||put(year+1,z2.);
else acyear=put(year-1,z2.)||substr(&checkterm,4,2);

file crstape;
put @001 &checkterm            /* campus year term month 120048 */
    @007 part_of_term_cd  $char03.  /* full part of term */ 
    @010 termtype    $char01.  /* X=extramural, Y= yearlong (GIS) blank=oncampus */
    @011 crs_coll    $char02.  /* offering course college -- as the student enrolled */
    @013 '1'                   /* offering campus */
    @014 crs_dept    $char03.  /* offering course dept -- as the student enrolled */
    @017 subject     $char04.  /* subject student enrolled in  */
    @021 crse        $char03.  /* course number as student enrolled */
	@024 '  ' 
	@026 sched_type_desc  $char30. /* description of sched type */
    @056 idtype      $char03.  /* decoded from section sched type   */
    @059 section     $char03.  /* section number  */
    @062 crn               5.  /* crn student enrolled in */
    @067 ctl_crn           5.  /* crn of controlling dept section */
    @072 cr_crn            5.  /* crn of credited dept */
    @077 cr_coll     $char02.  /* college of credited dept */
	@079 '1'                   /* credit campus */
    @080 cr_dept     $char03.  /* dept of credited CRN */
    @083 cr_subject  $char04.  /* subject of credited dept */
    @087 cr_crse     $char03.  /* crse number of credited crn */
	@090 '  '
    @092 crs_id      $char07.  /* course id */
	@099 '     ' 
    @104 stname      $char30.  /* student name, last first middle */
    @134 uin         $char09.  /* student UIN */
    @143 edw_pers_id 9.        /* edw person id, unique id in EDW */
    @152 sexcode     $char01.  /* M/F/U? or blank?*/
    @153 stuethnic   $char01.  /* student ethnic/race code */
    @155 cit_type    $char02.  /* citizenship type */
    @157 sttype      $char01.  /* student type, C=cont,G=1st time grad, T=ug tfer*/
    @158 stuclass    $char02.  /* original student class, blank for grad*/ 
	@160 ' ' 
    @161 stgrp           1.    /* 1-9 student class from edw or calculated from degree, level
	               1-4=fr-sr,5=NDeg ug,6=ndeg grad,7=masters,8=doc,9=prfl*/
    @162 stlevel     $char02.  /* student level code */
    @164 coststlvl   $char02.  /* LD, UD, G1, G2 */
    @166 program     $char12.  /* program 1 student is enrolled in */
    @178 acadcoll    $char02.  /* acad college of student (not Grad)*/
    @180 '1'                   /* campus of student */
    @181 dept        $char03.  /* acad dept of student  */
    @184 major       $char04.  /* major code of program 1 */
    @188 majorcip    $char06.  /* cip code of major */
    @194 conc        $char04.  /* first conc code of program 1 */
    @198 degree      $char05.  /* degree sought program 1 */
    @204 graded      $char01.  /* Y/N is section graded? */
    @205 sectcnt        1.     /* number of sections this crsid for this student */
    @206 secthrs        7.3     /* number of crdt hrs this seciton for this student*/
    @213 crn_bill_hour  7.3
    @220 crsIUS         2.     /* total crdt hrs + imputed hrs this crsid this student */
    @222 sectIUS        4.1    /* prorated IUS this st this section */
    @226 crscontacthrs  4.1    /* total contact hours for the course for the student*/
    @230 sectcontacthrs 4.1    /* contact hrs for the section  */
    @234 activity    $char02.  /* 01=on cmps instr, 41=cc onload, 42=xm onld,48=xm offld 49=cc offload */
    @236 fund        $char01.  /* 1=state, 2=ICR, 4=fed,, 9=other */
    @237 costrec     $char01.  /* cost recovery flag Y/N*/
    @238 imputeflag  1.        /*1=imputed ius, 0=not imputed */ 
    @239 'N'                   /* Y/N flag for contract courses */
    @240 Imethod     $char05.  /* for XM-OL type courses */
	@245 'N'                   /* Y/N flag for capital scholars */
	                           /* 246-296 reserved for campus use */ 
	@297 acyear      $char04.  /* e.g. 0405 */;
run;


*proc summary data=final2 sum n nway missing;
*class cr_crn stlevel stuclass;
*id term termtype graded;
*var sectius;
*output out=crstapesum sum=ius n=rgs;
*run;

*data test8;
*set final2;
*if crn=31774 or ctl_crn=31774;

*proc print ;
*var crn subject uin idtype ctl_crn first_ctl_crn;
*run;

*data test9;
*set final2;
*if crn=31774 or ctl_crn=31774;
*run;
*proc print;
*var crn subject uin idtype ctl_crn first_ctl_crn mtg_sched_type;
*run;

proc tabulate data=final3 missing;
title1 'Final summary of IUs after proration for' &checkterm;
var sectius secthrs;
class termtype imputeflag part_of_term_cd ;
table  all termtype*part_of_term_cd,(all imputeflag)*sectius*sum='';
run;

*proc contents data=final2;
*run;

proc sql;
drop table finsum1;
create table finsum1 as
  (select sum(sectius) as ius, 
            count(uin) as enr, 
                          ctl_crn,
                          idtype, 
                          stgrp /*1-4=fr-sr 5=nondeg ugrad 6=grad1 7=grad 2 8=ndeg gr 9=prfnl */
    from work.final3  group by ctl_crn,idtype,stgrp
    );



data finsum2;
set finsum1 ;
run;

proc download in=work out=work;
select finsum2 ;
run;
endrsubmit;


options noxwait xsync;  * noxwait closes the dos window when done;
                         *xsync delays next SAS cmd until system cmd is done.;
                         *xsync is needed to prevent DOS commands from piling up ;
run;



data finsum2;
set finsum2;
by ctl_crn idtype stgrp;
file crnsmtst;
if first.idtype then put @1 ctl_crn 5.  @6 idtype $char03. 
@10 "   0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0"
@;
nius=14*stgrp-4;
nenr=nius+7;
put @nius ius 6.1  @nenr enr 6.1 @;
if last.idtype then put @136 &checkterm @142 'x' ;
run;

data _NULL_;
path=&mappath ;
filenm='crnsum.txt';
versions=4;
%include 'h:\sas\utility\gdgupdt.inc'  ;
run;