* H:\sas\oracle\catsch\getsubjects.sas ;
* PN 2004/ ;
* create a lookup table of subject --> college & dept for a term ;
* needed to fix dept & coll on crosslisted course sections. ;
* run once at beginning of term  and then add subjects as needed later manually;


options nocenter errors=1 noxwait noxsync ls=80 obs=max ;


* location and name of output/input data files;
filename subjects   "h:\data\sil\YR200506\FA05\subjects.txt"  lrecl=50;
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('partofterm',"'  '") ;       *  part of term to select ;
run;

** get edw info ;
PROC sql ;
%include  'h:\carol\personal\connections\edwDirect.inc';
* has one statement;
*connect to oracle as edw (user=xxxxxx orapw=xxxxxx path='dsprod01');

* these are crns of courses offered this term mapped to the ctrlling dept subject & number;

create view subjects as select * from connection to EDW
(select distinct crs_subj_cd as subject, count(crs_nbr) as n, coll_cd,dept_cd,dept_name
from EDW.T_CRS
  where  term_cd like &checkterm 
  group by crs_subj_cd,coll_cd,dept_cd,dept_name
    );
disconnect from EDW;
quit;

data subjects1; 
set subjects;  
 run;          
** here is where you can add in subjects that are secondary only. setting n=0 ensures any newer info will override these ;
data extrasubjects;
input @01  subject $char04. @6 coll_cd  $char02.  @9 dept_cd $char04. @14 dept_name $char30. ;
n=0;
cards;
CSE  KP 1246 Computational Sci & Eng                
GLBL LQ 1505 Global Studies                         
TMGT KP 1545 Technology Management                  
run;


data subjects2a;
set subjects1 extrasubjects;

proc sort;
by subject descending n;

data subjects2;
set subjects2a;
by subject;
if first.subject;
run;


data _NULL_;  
set subjects2;
file subjects;
put @01 subject              $char04. 
    @06 coll_cd              $char02.
    @09 dept_cd              $char04. /* campus-dept -- may use only 3-digit later */
    @14 dept_name            $char30.
    @50 'x'             ;
	run;
proc print;
run;