//BRXXXLOD JOB (BRXXX,,,99),'UUUU SFA LOAD',TIME=(1),REGION=0M,                 

//  LINES=10000                                                                 

//*************************************************************/                

//****   PROGRAM TO CONVERT LEGACY STUDENT FINANCIAL AID    ***/                

//****        FILE LAYOUT TO NEW DATABASE LAYOUT            ***/                

//*************************************************************/                

//****            *** MAINTENANCE ***                       ***/                

//**** CHANGE THE JOB CARD TO REFLECT YOUR ENVIRONMENT      ***/                

//****                                                      ***/                

//**** CHANGE THE INPUT FILE NAME TO YOUR INPUT FILE NAME   ***/                

//****                                                      ***/                

//**** CHANGE THE FOUR OUTPUT FILE NAMES AND THE FILE NAMES ***/                

//****    IN THE HOUSEKEEPING STEP TO YOUR FILE NAMES       ***/                

//************   HOUSEKEEPING  ********************************/                

//DELETE  EXEC PGM=IDCAMS,REGION=512K                                           

//SYSPRINT DD SYSOUT=A                                                          

//SYSIN DD *                                                                    

  DELETE (BOR.UUUU.SFA.WOVBAR)                                                  

  DELETE (BOR.UUUU.SFA.PERDEMO)                                                 

  DELETE (BOR.UUUU.SFA.FADEMO)                                                  

  DELETE (BOR.UUUU.SFA.FINAID.AWARDS)                                           

  SET MAXCC = 0                                                                 

/*                                                                              

//STEP1     EXEC SAS,WORK='20000,10000'                                         

//*---------------  INPUT FILES -----------------*/                             

//SFAIN   DD DSN=BOR.MASTER.FINAID.UUUU.Y0607,DISP=SHR                          

//*--------------- OUTPUT FILES  ---------------- */                            

//SFAWOBAR DD DSN=BOR.UUUU.SFA.WOVBAR,                                          

//            DISP=(,CATLG,DELETE),UNIT=SYSDA,                                  

//            DCB=(LRECL=1136,RECFM=VB,BLKSIZE=),                               

//            SPACE=(CYL,(20,5),RLSE)                                           

//PERDEMO  DD DSN=BOR.UUUU.SFA.PERDEMO,                                         

//            DISP=(,CATLG,DELETE),UNIT=SYSDA,                                  

//            DCB=(LRECL=200,RECFM=FB,BLKSIZE=),                                

//            SPACE=(CYL,(10,5),RLSE)                                           

//SFADEMO  DD DSN=BOR.UUUU.SFA.FADEMO,                                          

//            DISP=(,CATLG,DELETE),UNIT=SYSDA,                                  

//            DCB=(LRECL=600,RECFM=FB,BLKSIZE=),                                

//            SPACE=(CYL,(20,5),RLSE)                                           

//SFAWARDS DD DSN=BOR.UUUU.SFA.FINAID.AWARDS,                                   

//            DISP=(,CATLG,DELETE),UNIT=SYSDA,                                  

//            DCB=(LRECL=600,RECFM=FB,BLKSIZE=),                                

//            SPACE=(CYL,(30,9),RLSE)                                           

                                                                                

OPTIONS  S=72 PS=65 ERRORS=3 MISSING = ' ' NODATE NOSOURCE;                     

* REMOVES ANY VERTICAL BARS FROM THE INCOMING FILE;                             

 DATA SFABAR;                                                                   

      INFILE SFAIN LENGTH=LEN TRUNCOVER;                                        

      INPUT @1 RECORD $1136.;                                                   

      FORMAT RECORD2 $1136.;                                                    

      RECORD2=TRANSLATE(RECORD,' ','|');                                        

      FILE SFAWOBAR LRECL=1136;                                                 

      PUT @1 RECORD2;                                                           

                                                                                

DATA SFAMAIN (KEEP = FISYR SSN UNIV SEX DPSTAT RACE BDATE FAMINC SRES           

                     FAMCON URESV1 SCLAS  STYPE NEEDBASE PROGCIP NEED           

                     FILCLSB FILCLSI SEGCNT)                                    

                                                                                

     SFASEG  (KEEP = FISYR SSN UNIV SEX DPSTAT RACE BDATE AWPROG AWCOND         

                     FNDS SUM1AMT FALLAMT SPRGAMT SUM2AMT FILAWDB);             

                                                                                

 INFILE SFAWOBAR LENGTH=LEN TRUNCOVER;                                          

  INPUT   @001  FISYR     $CHAR004.   @005 UNIV      $CHAR004.                  

          @009  SSN       $CHAR009.   @018 BDATE     $CHAR010.                  

          @028  FILLER    $CHAR004.   @032 RACE      $CHAR001.                  

          @033  SEX       $CHAR001.   @034 DPSTAT    $CHAR001.                  

          @035  FAMINC    ZD7.        @042 FAMCON    ZD7.                       

          @052  URESV1 $CHAR10.                                                 

          @062  SRES      $CHAR001.   @063 STYPE     $CHAR001.                  

          @064  SCLAS     $CHAR001.   @065 PROGCIP   $CHAR006.                  

          @071  NEEDBASE  $CHAR001.   @072 NEED      ZD7.                       

          @079  FILCLSB   $CHAR012.   @091 FILCLSI   $CHAR020.                  

          @111  SEGCNT    ZD2. @;                                               

 OUTPUT SFAMAIN;                                                                

 IF SEGCNT GT 0;                                                                

    N = 0;                                                                      

 LOOP:                                                                          

    N+1; IF (SEGCNT LT 1) OR (N GT SEGCNT) THEN GOTO RESOUT;                    

         IF N LE SEGCNT THEN                                                    

     INPUT                                                                      

         AWPROG  $CHAR03.   AWCOND  $CHAR01.                                    

         FNDS    $CHAR01.   SUM1AMT     ZD9.2  FALLAMT     ZD9.2                

         SPRGAMT     ZD9.2  SUM2AMT     ZD9.2  FILAWDB $CHAR10. @;              

      OUTPUT SFASEG;                                                            

                                                                                

 GOTO LOOP;                                                                     

 RESOUT:                                                                        

 RETURN;                                                                        

                                                                                

 /**********************************************************                    

  *                 PERSON DEMOGRAPHICS                    *                    

  *********************************************************/                    

                                                                                

  DATA DEMO (KEEP = FIRSTYR DEMO_TIME_FRAME REPT_INST DEMO_DATA_SOURCE          

                   PERSON_ID PID_VALID_SSN NAME_LAST NAME_FIRST                 

                   NAME_MI  SUFFIX GENDER RACE_ETHNICITY HISPANIC_FLG           

                   AM_IND_ALASKAN_FLG ASIAN_FLG BLACK_AFRICAN_AM_FLG            

                   NAT_HAWAII_PAC_IS_FLG WHITE_FLG NON_RES_ALIEN_FLG            

                   NO_RACE_REPORT_FLG  U_RESV_1 DT_BIRTH_YYYYMMDD               

                   UNIV_ROW_ID)                                                 

                                                                                

      FINDEMO (KEEP = REPT_TIME_FRAME CURR_DEPEND_STAT FAMILY_INCOME            

                      FAMILY_CONTRIB STU_ENTRY_TYP STU_CLASS_LEVEL              

                      CIP_STUDENT  FINAID_NEED U_RESV_2 U_RESV_1                

                      UNIV_ROW_ID FEE_CLASS_RES                                 

                      DEMO_TIME_FRAME REPT_INST DEMO_DATA_SOURCE                

                      PERSON_ID PID_VALID_SSN NAME_LAST NAME_FIRST);            

   SET SFAMAIN;                                                                 

   FORMAT FIRSTYR $CHAR04. DEMO_TIME_FRAME $CHAR08. REPT_INST $CHAR04.          

          GOODOBS 8. DEMO_DATA_SOURCE $CHAR08. CURR_DEPEND_STAT $CHAR01.        

          FAMILY_INCOME 7. FAMILY_CONTRIB 7. FEE_CLASS_RES $CHAR01.             

          STU_ENTRY_TYP $CHAR01. STU_CLASS_LEVEL $CHAR01. FINAID_NEED 7.        

          U_RESV_1 $CHAR50. U_RESV_2 $CHAR50. PID_VALID_SSN $CHAR01.            

          PERSON_ID $CHAR09. UNIV_ROW_ID $CHAR08.;                              

                                                                                

   FIRSTYR               =FISYR-1;                                              

   DEMO_TIME_FRAME       = FIRSTYR||FISYR;                                      

   REPT_INST             = UNIV;                                                

   DEMO_DATA_SOURCE      = 'SFA';                                               

   PERSON_ID             = SSN;                                                 

   PID_VALID_SSN         = 'Y';                                                 

   NAME_LAST             = '.';                                                 

   NAME_FIRST            = '.';                                                 

   NAME_MI               = '.';                                                 

   SUFFIX                = '.';                                                 

   NAME_MI               = '.';                                                 

   SUFFIX                = '.';                                                 

   GENDER                = SEX;                                                 

   RACE_ETHNICITY        = RACE;                                                

   HISPANIC_FLG          = '.';                                                 

   AM_IND_ALASKAN_FLG    = '.';                                                 

   ASIAN_FLG             = '.';                                                 

   BLACK_AFRICAN_AM_FLG  = '.';                                                 

   NAT_HAWAII_PAC_IS_FLG = '.';                                                 

   WHITE_FLG             = '.';                                                 

   NON_RES_ALIEN_FLG     = '.';                                                 

   NO_RACE_REPORT_FLG    = '.';                                                 

   U_RESV_1              = '.';                                                 

   FORMAT DT_BIRTH_YYYYMMDD $CHAR08.;                                           

   DOB_YEAR = SUBSTR(BDATE,1,4);                                                

   DOB_MONTH = SUBSTR(BDATE,6,2);                                               

   DOB_DAY = SUBSTR(BDATE,9,2);                                                 

   DT_BIRTH_YYYYMMDD = TRIM(DOB_YEAR)||TRIM(DOB_MONTH)||TRIM(DOB_DAY);          

   UNIV_ROW_ID =  PUT(_N_,Z8.);                                                 

   OUTPUT DEMO;                                                                 

                                                                                

   /**********************************************************                  

    *      FINANCIAL AID DEMO                                *                  

    *********************************************************/                  

      FORMAT GOODOBS_FDEMO 8.; RETAIN GOODOBS_FDEMO;                            

      REPT_TIME_FRAME        = FIRSTYR||FISYR;                                  

      CURR_DEPEND_STAT       = DPSTAT;                                          

      FAMILY_INCOME          = FAMINC;                                          

      FAMILY_CONTRIB         = FAMCON;                                          

      FEE_CLASS_RES          = SRES;                                            

      STU_ENTRY_TYP          = STYPE;                                           

      STU_CLASS_LEVEL        = SCLAS;                                           

      CIP_STUDENT            = PROGCIP;                                         

      FINAID_NEED            = NEED;                                            

      U_RESV_2               = FILCLSI;                                         

                                                                                

      IF FAMILY_INCOME       = 9999999 THEN FAMILY_INCOME  = '.';                  

      IF FAMILY_CONTRIB      = 9999999 THEN FAMILY_CONTRIB = '.';                 

      IF FINAID_NEED         = 9999999 THEN FINAID_NEED    = '.';                 

                                                                                

      OUTPUT FINDEMO;                                                           

                                                                                

   /*******************************************************                     

    *             FINANCIAL AID AWARDS                    *                     

    ******************************************************/                     

  PROC SORT DATA = SFASEG; BY FISYR UNIV SSN AWPROG AWCOND;                           

                                                                                

  PROC SUMMARY DATA=SFASEG; BY FISYR UNIV SSN AWPROG AWCOND;                          

                VAR FALLAMT SPRGAMT SUM1AMT SUM2AMT;                            

                 ID AWCOND FNDS DPSTAT;                                         

             OUTPUT OUT=SFASSN SUM=;                                            

  DATA FINAL (KEEP = DEMO_TIME_FRAME REPT_TIME_FRAME REPT_INST                  

                   PERSON_ID U_RESV_1 DEMO_DATA_SOURCE                          

                   AWARD_PAYMENT_TERM AWARD_PROG_ID AWARD_COND_ID               

                   DEMO_TIME_FRAME SOURCE_FUNDS FIN_AID_AMT_PAID                

                   PK_SEQ_NUM_FIN_AWARDS) ;                                     

 SET SFASSN;                                                                    

   FORMAT FIRSTYR $CHAR04. AWARD_PAYMENT_TERM $CHAR08.                          

          AWARD_PROG_ID $CHAR03. AWARD_COND_ID $CHAR01. U_RESV_1                

          $CHAR50. GOODOBS_FIN 8.; RETAIN GOODOBS_FIN;                          

          FIRSTYR=FISYR-1;                                                      

 * TIME FRAME FORMATS ARE ACADEMIC YEAR 20062007;                               

   DEMO_TIME_FRAME     = FIRSTYR||FISYR;                                        

   REPT_TIME_FRAME     = FIRSTYR||FISYR;                                        

   REPT_INST           = UNIV;                                                  

   DEMO_DATA_SOURCE    = 'SFA';                                                 

   PERSON_ID           = SSN;                                                   

   AWARD_PROG_ID       = AWPROG;                                                

   AWARD_COND_ID       = AWCOND;                                                

   SOURCE_FUNDS        = FNDS;                                                  

   CURR_DEPEND_STAT    = DPSTAT;                                                

   U_RESV_1         = FILAWDB;                                                  

   UNIV_ROW_ID =  PUT(_N_,Z8.);                                                 

   IF GOODOBS_FIN < 1  THEN DO;                                                 

      FORMAT PK_SEQ_NUM_FIN_AWARDS 8.;                                          

      PK_SEQ_NUM_FIN_AWARDS = 0;                                                

   END;                                                                         

      GOODOBS_FIN + 1;                                                          

   IF FALLAMT > 0 THEN DO;                                                      

      AWARD_PAYMENT_TERM  = FIRSTYR||'08';                                      

      FIN_AID_AMT_PAID    = FALLAMT;                                            

      PK_SEQ_NUM_FIN_AWARDS = PK_SEQ_NUM_FIN_AWARDS + 1;                        

      RETAIN PK_SEQ_NUM_FIN_AWARDS;  OUTPUT FINAL;END;                          

   IF SPRGAMT > 0 THEN DO;                                                      

      AWARD_PAYMENT_TERM = FISYR||'01';                                         

      FIN_AID_AMT_PAID = SPRGAMT;                                               

      PK_SEQ_NUM_FIN_AWARDS = PK_SEQ_NUM_FIN_AWARDS + 1;                        

      RETAIN PK_SEQ_NUM_FIN_AWARDS;  OUTPUT FINAL;END;                          

   IF SUM1AMT > 0 THEN DO;                                                      

      AWARD_PAYMENT_TERM = FIRSTYR||'05';                                       

      FIN_AID_AMT_PAID = SUM1AMT;                                               

      PK_SEQ_NUM_FIN_AWARDS = PK_SEQ_NUM_FIN_AWARDS + 1;                        

      RETAIN PK_SEQ_NUM_FIN_AWARDS;  OUTPUT FINAL;END;                          

   IF SUM2AMT > 0 THEN DO;                                                      

      AWARD_PAYMENT_TERM = FISYR||'05';                                         

      FIN_AID_AMT_PAID   = SUM2AMT;                                             

      PK_SEQ_NUM_FIN_AWARDS = PK_SEQ_NUM_FIN_AWARDS + 1;                        

      RETAIN PK_SEQ_NUM_FIN_AWARDS;  OUTPUT FINAL;END;                          

                                                                                

 /*********************************************************************         

 *                   PRINT ROUTINES                                   *         

 **********************************************************************/        

                                                                                

 /* PERSON_DEMO TABLE*/                                                         

  DATA _NULL_; SET DEMO;                                                        

      FILE PERDEMO DLM='|'  LRECL=200;                                          

                                                                                

  PUT UNIV_ROW_ID DEMO_TIME_FRAME REPT_INST DEMO_DATA_SOURCE PERSON_ID          

      PID_VALID_SSN  DT_BIRTH_YYYYMMDD NAME_LAST NAME_FIRST                     

      NAME_MI SUFFIX GENDER RACE_ETHNICITY HISPANIC_FLG                         

      AM_IND_ALASKAN_FLG ASIAN_FLG BLACK_AFRICAN_AM_FLG                         

      NAT_HAWAII_PAC_IS_FLG WHITE_FLAG N_RES_ALIEN_FLG                          

      NO_RACE_REPORT_FLG U_RESV_1;                                              

                                                                                

   /* FINANCIAL_AID_DEMO TABLE */                                               

   DATA _NULL_; SET FINDEMO;                                                    

   FILE SFADEMO DLM='|' LRECL=600;                                              

                                                                                

                                                                                

   PUT UNIV_ROW_ID REPT_TIME_FRAME REPT_INST PERSON_ID                          

   DEMO_TIME_FRAME DEMO_DATA_SOURCE CURR_DEPEND_STAT FAMILY_INCOME              

   FAMILY_CONTRIB FEE_CLASS_RES STU_ENTRY_TYP STU_CLASS_LEVEL                   

   CIP_STUDENT FINAID_NEED U_RESV_1 U_RESV_2;                                   

                                                                                

   /* FINANCIAL_AID_AWARDS */                                                   

   PROC SORT DATA = FINDEMO; BY REPT_TIME_FRAME REPT_INST PERSON_ID             

                    DEMO_TIME_FRAME;                                            

   PROC SORT DATA = FINAL; BY REPT_TIME_FRAME REPT_INST PERSON_ID               

                    DEMO_TIME_FRAME;                                            

   DATA BOTH;                                                                   

        MERGE FINDEMO(IN=A) FINAL(IN=B);                                        

        BY REPT_TIME_FRAME REPT_INST PERSON_ID DEMO_TIME_FRAME;                 

        IF A AND B;                                                             

        IF B;                                                                   

                                                                                

   DATA _NULL_; SET BOTH;                                                       

     FILE SFAWARDS DLM='|' LRECL=600;                                           

     IF AWARD_PAYMENT_TERM = ' ' THEN                                           

        AWARD_PAYMENT_TERM = '.';                                               

     IF AWARD_PROG_ID = ' ' THEN                                                

        AWARD_PROG_ID = '.';                                                    

     IF AWARD_COND_ID = ' ' THEN                                                

        AWARD_COND_ID = '.';                                                    

                                                                                

    FORMAT UNIV_ROW_ID $CHAR08.;                                                

    UNIV_ROW_ID = PUT(PK_SEQ_NUM_FIN_AWARDS,Z8.);                               

                                                                                

   PUT    UNIV_ROW_ID                                                           

   REPT_TIME_FRAME REPT_INST PERSON_ID AWARD_PAYMENT_TERM                       

   AWARD_PROG_ID AWARD_COND_ID DEMO_TIME_FRAME DEMO_DATA_SOURCE                 

   SOURCE_FUNDS FIN_AID_AMT_PAID U_RESV_1;