Re: excel processing taking long time

anirudhs · Posted 11-13-2017 07:33 AM

Hi my code of importing data from excel and to reach the final output the processing is taking mins for 930407 rows of data.

how can i reduce this time and optimize and atomize the process.

even i need base sas code to rename the excel file in location as the excel file is in .xlsb which is to be renamed with same name with extion as .xlsx

Thanks

Kurt_Bremser · Posted 11-13-2017 07:41 AM

Run your code with options fullstimer; and post the resulting log here. Use the {i} icon for posting the log.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

anirudhs · Posted 11-13-2017 08:29 PM

Hi Kurt, Yes i will use fulls timer and send you the log file.
yes the proc import is been use and its mess.

Kurt_Bremser · Posted 11-14-2017 01:47 AM

Given the sheer size of ~ 1 million rows, I have some doubts that your data originates from Excel; I suspect someone is just abusing the Excel file format for something it was never meant for.

Data of this size should always be transported in an open, easily readable, textual format, either with fixed columns or delimiters (CSV).

Since we know that a data step that reads tetual data will always perform very well, we can then look for other reasons, once the "Excel to something sensible" conversion has been done with (if the performance problem still persists).

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

anirudhs · Posted 11-14-2017 02:03 AM

Hi KurtBremser,

I am sending you the log file attached with this reply its a pdf of 37 page.

please guide

Kurt_Bremser · Posted 11-14-2017 02:37 AM

Well, the import from xlsx takes what it takes. Since there's no difference between real and CPU time, there is nothing you can do about it, aside from switching to a data format that makes sense (see other posts in the thread).

Later on, in the SQL that creates the 930407 rows, I can see that real time exceeds CPU time by a factor of 2, indicating I/O waits most probably caused by the network (your libraries COLLEC, SASVACL, AMAY all reside on a remote server).

Then I see a real "bummer" when accessing library FINTEST (lines 313 to 317). There you have a CPU time of ~ 5 seconds, but a real time of 6(!) minutes(!). Since the libname for that library is not part of your log, I can't say with 100% certainty it's the network, but I suspect it is. Similar happens from library FINRL (lines 375 ff.).

Then look at lines 577 to 583. There you have another step that takes just ~ 3 CPU seconds, but 5:28 minutes real time.

Bottom line: Move processing to local libraries, so that network I/O waits are eliminated. Change from .xlsx files to CSV or similar, and use properly written data steps for the import. Take control of the process, don't rely on the guesses of proc import.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

RW9 · Posted 11-13-2017 08:37 AM

Several things here. First, 930407 rows is a lot of data, why is it in Excel? Possibly the worst medium for data. It will take a while to import that amount of data from such a poor format.

Secondly, you say you have an XLSB file - this is a proprietary binary file format. You cannot just rename it XLSX - which is a zipped Open Office file format - and expect it to work. This is likely why your process is taking so long.

My recommendations, in order of preference (note when I say save I mean open it in Excel and use Save As):

1) Save the file to CSV - this is a plain text delimited file. It will be quicker to import, and you can write a datastep import program to read in the data correctly (since Excel is a poor data medium, and I assume your using proc import which is a guessing procedure = total mess).

2) Save the file as XLSX format. This removes the whole binary part and should be quicker, although your still stuck with poor data medium, and I assume you would use proc import still = a mess.

Reeza · Posted 11-13-2017 01:02 PM

And at minimum, you should at least explain how you're importing the data.

Is it PROC IMPORT, a custom macro, data step (which would be likely wrong), libname with XLSX or libname with PCFiles....

@anirudhs wrote:

Hi my code of importing data from excel and to reach the final output the processing is taking mins for 930407 rows of data.

how can i reduce this time and optimize and atomize the process.

even i need base sas code to rename the excel file in location as the excel file is in .xlsb which is to be renamed with same name with extion as .xlsx

Thanks

anirudhs · Posted 11-13-2017 08:26 PM

the person who designed the code he himself used the proc import for importing the excel into the studio and there are 3 files of same rows of data .

anirudhs · Posted 11-14-2017 02:37 AM

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 57         
 58         /*  */
 59         /* Creating two libraries one for Source data and the another one for output Sasdatsets. */
 60         /* Collec is Source Library and SASVACL is permanent library to store SASdatasets */
 61         
 62         
 63         /* This libname statement is to pull excel file */
 64         
 65         libname collec '\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES';
 NOTE: Libref COLLEC was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: \\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES
 66         
 67         /* This libname statement is for output sas datasets */
 68         
 69         libname SASVACL'\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\SAS_Data';
 NOTE: Libref SASVACL was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: \\\\10.4.1.197\\CollectionAnalysis\\Karthik\\SAS_Data
 70         
 71         options fullstimer;
 72         
 73         /* This is written to import an Excel file 'DAC' */
 74         options validvarname = any;
 75         proc import out = SASVACL.DAC_Ecollect (Rename= ('physicalReceiptNo/Online Transac'n = PhysicalReceiptNo))
 76         datafile="\\\\10.4.1.162\\ecollect\\DAC\\DAC.xlsx"
 77         DBMS=xlsx REPLACE;
 78         sheet= 'MTD CCS';
 79         Getnames=Yes;
 80         run;
 
 NOTE: The import data set has 111511 observations and 60 variables.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           1:39.48
       user cpu time       1:34.59
       system cpu time     0.73 seconds
       memory              36636.87k
       OS Memory           63256.00k
       Timestamp           11/14/2017 11:39:17 AM
       Step Count                        4  Switch Count  42
       
 NOTE: SASVACL.DAC_ECOLLECT data set was successfully created.
 
 81         
 82         
 83         
 84         Data SASVACL.DAC_Ecollect ;
 85         Set SASVACL.DAC_Ecollect ;
 V$0V$1
 87         Run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       86:26   
 NOTE: There were 111511 observations read from the data set SASVACL.DAC_ECOLLECT.
 NOTE: The data set SASVACL.DAC_ECOLLECT has 111511 observations and 61 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.40 seconds
       user cpu time       0.10 seconds
       system cpu time     0.25 seconds
       memory              637.18k
       OS Memory           24140.00k
       Timestamp           11/14/2017 11:39:18 AM
       Step Count                        5  Switch Count  51
       
 
 88         
 89         /* This is written to import an Excel file 'Cheque_Status' */
 90         /*  */
 91         proc import out = SASVACL.Cheque_Status datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Cheque_Status.xlsx"
 92         DBMS=xlsx REPLACE;
 93         sheet= 'Data';
 94         Getnames=Yes;
 95         run;
 
 NOTE: The import data set has 2649 observations and 26 variables.
 NOTE: SASVACL.CHEQUE_STATUS data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           1.11 seconds
       user cpu time       1.04 seconds
       system cpu time     0.03 seconds
       memory              1105.50k
       OS Memory           24140.00k
       Timestamp           11/14/2017 11:39:19 AM
       Step Count                        6  Switch Count  40
       
 
 96         
 97         
 98         /* This is to concatenate 4 columns into one to get unique values in DAC */
 99         Options Compress = Yes;
 100        data SASVACL.Concatenate;
 101        set  SASVACL.DAC_Ecollect;
 102        Concatenated = Cats(Reference_NO, TotalReceiptAmount, InstrumentNo, ReceiptNo);
 103        Concatenated2 = Cats(Reference_NO, TotalReceiptAmount, InstrumentNo,PhysicalReceiptNo);
 104        run;
 NOTE: DATA statement used (Total process time):
       real time           0.66 seconds
       user cpu time       0.43 seconds
       system cpu time     0.20 seconds
       memory              768.93k
       OS Memory           24140.00k
       Timestamp           11/14/2017 11:39:20 AM
       Step Count                        7  Switch Count  33
       
 NOTE: There were 111511 observations read from the data set SASVACL.DAC_ECOLLECT.
 NOTE: The data set SASVACL.CONCATENATE has 111511 observations and 63 variables.
 NOTE: Compressing data set SASVACL.CONCATENATE decreased size by 67.78 percent. 
       Compressed is 599 pages; un-compressed would require 1859 pages.
 
 105        
 106        /* This is to concatenate 4 columns into one to get unique values in Cheque_Status only when Status= 'Clear'*/
 107        
 108        Options Compress = Yes;
 109        data SASVACL.Cheque_Concat;
 110        set SASVACL.cheque_status;
 111        if Status = 'Clear' or Status = 'CLEAR'  then
 112        Concatenated3 = cats(compress(AgreementNo,'', 'kd'), ChequeAmt, ChequeNum, ReceiptNo);
 113        else Concatenated3 = '0';
 114        keep Concatenated3 ;
 115        run;
 
 NOTE: There were 2649 observations read from the data set SASVACL.CHEQUE_STATUS.
 NOTE: The data set SASVACL.CHEQUE_CONCAT has 2649 observations and 1 variables.
 NOTE: Compressing data set SASVACL.CHEQUE_CONCAT decreased size by 66.67 percent. 
       Compressed is 3 pages; un-compressed would require 9 pages.
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              567.78k
       OS Memory           24140.00k
       Timestamp           11/14/2017 11:39:20 AM
       Step Count                        8  Switch Count  55
       
 
 116        
 117        
 118        /* Mapping DAC and Cheque_Status Using Left Join to create a new  column Check1 */
 119        
 120        Options Compress = Yes;
 121        Proc Sql;
 122        Create table SASVACL.Dac_Checque as
 123        select * from SASVACL.Concatenate c Left Join SASVACL.Cheque_Concat d
 124        on c.Concatenated = d.Concatenated3 ;
 NOTE: Compressing data set SASVACL.DAC_CHECQUE decreased size by 72.61 percent. 
       Compressed is 599 pages; un-compressed would require 2187 pages.NOTE: Table SASVACL.DAC_CHECQUE created, with 111511 rows and 
                                                                             64 columns.
 
 
 125        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           1.71 seconds
       user cpu time       1.09 seconds
       system cpu time     0.92 seconds
       memory              152364.65k
       OS Memory           175136.00k
       Timestamp           11/14/2017 11:39:21 AM
       Step Count                        9  Switch Count  50
       
 
 126        
 127        
 128        /* Here Check1 Variable is created */
 129        
 130        Options Compress = Yes;
 131        Data SASVACL.Check1(drop = Concatenated3);
 132        set SASVACL.Dac_Checque;
 V#0133        if Concatenated = Concatenated3 then
 134        Check1= 1;
 135        else check1 = 0;
 136        run;
 
 NOTE: There were 111511 observations read from the data set SASVACL.DAC_CHECQUE.
 NOTE: The data set SASVACL.CHECK1 has 111511 observations and 64 variables.
 NOTE: Compressing data set SASVACL.CHECK1 decreased size by 67.62 percent. 
       Compressed is 602 pages; un-compressed would require 1859 pages.
 NOTE: DATA statement used (Total process time):
       real time           0.91 seconds
       user cpu time       0.74 seconds
       system cpu time     0.09 seconds
       memory              1020.40k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:22 AM
       Step Count                        10  Switch Count  58
       
 
 137        
 138        /* Mapping New Dataset which is created above and Cheque_Status Using Left Join to create a new  column Check2 */
 139        
 140        Options Compress= yes;
 141        Proc Sql;
 142        Create table SASVACL.Dac_Checque2 as
 143        select * from SASVACL.Check1 c Left Join SASVACL.Cheque_Concat d
 144        on c.Concatenated2 = d.Concatenated3;
 NOTE: Compressing data set SASVACL.DAC_CHECQUE2 decreased size by 73.02 percent. 
       Compressed is 602 pages; un-compressed would require 2231 pages.NOTE: Table SASVACL.DAC_CHECQUE2 created, with 111511 rows 
                                                                             and 65 columns.
 
 
 145        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           1.67 seconds
       user cpu time       1.02 seconds
       system cpu time     0.82 seconds
       memory              152847.09k
       OS Memory           176580.00k
       Timestamp           11/14/2017 11:39:24 AM
       Step Count                        11  Switch Count  58
       
 
 146        
 147        /* Check2 column is created here */
 148        
 149        Options Compress = Yes;
 150        Data SASVACL.Check2;
 V#0151        set SASVACL.Dac_Checque2;
 152        if Concatenated2 = Concatenated3 then
 153        Check2= 1;
 154        else Check2 = 0;
 155        run;
 
 NOTE: There were 111511 observations read from the data set SASVACL.DAC_CHECQUE2.
 NOTE: The data set SASVACL.CHECK2 has 111511 observations and 66 variables.
 NOTE: Compressing data set SASVACL.CHECK2 decreased size by 73.02 percent. 
       Compressed is 602 pages; un-compressed would require 2231 pages.
 NOTE: DATA statement used (Total process time):
       real time           0.89 seconds
       user cpu time       0.74 seconds
       system cpu time     0.14 seconds
       memory              1021.00k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:25 AM
       Step Count                        12  Switch Count  56
       
 
 156        
 157        /* Importing Payment */
 158        proc import out = SASVACL.Payment_Mode datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Payment_Mode.xlsx"
 159        DBMS=xlsx REPLACE;
 160        sheet= 'Sheet1';
 161        Getnames=Yes;
 162        run;
 
 NOTE: The import data set has 38 observations and 4 variables.
 NOTE: Compressing data set SASVACL.PAYMENT_MODE increased size by 100.00 percent. 
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.PAYMENT_MODE data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.06 seconds
       user cpu time       0.04 seconds
       system cpu time     0.01 seconds
       memory              647.75k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:25 AM
       Step Count                        13  Switch Count  44
       
 
 163        
 164        /* Mapping Check2 data with Payment_Mode to get CAT and Status Values */
 165        
 166        Options Compress = Yes;
 167        Proc Sql;
 168        Create table SASVACL.Payment as
 169        select * from SASVACL.Check2 c Left Join SASVACL.Payment_Mode d
 170        on c.PaymentMode = d.PaymentMode;
 WARNING: Variable PaymentMode already exists on file SASVACL.PAYMENT.
 NOTE: Compressing data set SASVACL.PAYMENT decreased size by 72.50 percent. 
       Compressed is 626 pages; un-compressed would require 2276 pages.NOTE: Table SASVACL.PAYMENT created, with 111511 rows and 69 
                                                                             columns.
 
 
 171        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           1.67 seconds
       user cpu time       1.10 seconds
       system cpu time     0.81 seconds
       memory              164284.84k
       OS Memory           188096.00k
       Timestamp           11/14/2017 11:39:27 AM
       Step Count                        14  Switch Count  60
       
 
 172        
 173        /* Filling Status Field with 'clear' where Cat is'CHEQUE'  and status is blank */
 174        
 175        Options Compress = Yes;
 176        Data SASVACL.Status;
 V#0V#1
 178        If Cat = 'CHEQUE' and check1 =1 or check2 = 1 then
 179        Status = 'Clear';
 180        Else Status = Status;
 181        if BounceChargesAmt =. then BounceChargesAmt = 0;
 182        Else BounceChargesAmt=BounceChargesAmt;
 183        if LatePaymentPenalty =. then LatePaymentPenalty = 0;
 184        Else LatePaymentPenalty=LatePaymentPenalty;
 185        Penal_Bounce = LatePaymentPenalty + BounceChargesAmt;
 186        Physical_Collection = TotalReceiptAmount - Penal_Bounce;
 187        run;
 NOTE: There were 111511 observations read from the data set SASVACL.PAYMENT.
 NOTE: The data set SASVACL.STATUS has 111511 observations and 71 variables.
 NOTE: Compressing data set SASVACL.STATUS decreased size by 72.45 percent. 
       Compressed is 627 pages; un-compressed would require 2276 pages.
 NOTE: DATA statement used (Total process time):
       real time           0.70 seconds
       user cpu time       0.54 seconds
       system cpu time     0.09 seconds
       memory              1021.06k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:28 AM
       Step Count                        15  Switch Count  61
       
 
 188        
 189        Proc Sql;
 190        Create Table SASVACL.Update_PaymentModeMaster as
 191        Select Distinct(PaymentMode)
 192        from SASVACL.Status
 193        where Cat = '';
 NOTE: Compressing data set SASVACL.UPDATE_PAYMENTMODEMASTER increased size by 100.00 percent. 
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: Table SASVACL.UPDATE_PAYMENTMODEMASTER created, with 1 rows and 1 columns.
 
 194        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.29 seconds
       user cpu time       0.23 seconds
       system cpu time     0.06 seconds
       memory              3391.56k
       OS Memory           28764.00k
       Timestamp           11/14/2017 11:39:28 AM
       Step Count                        16  Switch Count  45
       
 
 195        
 196        Proc Export
 197        Data = SASVACL.Update_PaymentModeMaster
 198        Outfile = "\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\Update_Master_Files\\PaymentMode_UPADATE"
 199        DBMS = XLSX
 200        Replace;
 201        Run;
 
 NOTE: The export data set has 1 observations and 1 variables.
 NOTE: "\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\Update_Master_Files\\PaymentMode_UPADATE.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.14 seconds
       user cpu time       0.00 seconds
       system cpu time     0.06 seconds
       memory              1092.26k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:28 AM
       Step Count                        17  Switch Count  46
       
 
 202        
 203        /* Summarizing the data by Reference_NO and CAT where status is Clear */
 204        
 205        Options Compress = Yes;
 206        Proc Sql;
 207        create table SASVACL.Summarized as
 208        Select Reference_NO, Cat, Sum(Physical_Collection) as PhysicalCollection
 209        from SASVACL.Status
 210        where Status = 'Clear' and Flag = 1
 211        Group by Reference_NO, cat;
 NOTE: Compressing data set SASVACL.SUMMARIZED increased size by 4.55 percent. 
       Compressed is 23 pages; un-compressed would require 22 pages.
 NOTE: Table SASVACL.SUMMARIZED created, with 44215 rows and 3 columns.
 
 212        Quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.34 seconds
       user cpu time       0.28 seconds
       system cpu time     0.10 seconds
       memory              8531.28k
       OS Memory           32432.00k
       Timestamp           11/14/2017 11:39:28 AM
       Step Count                        18  Switch Count  55
       
 
 213        
 214        /* Sorting the data which is used to Transpose */
 215        
 216         Proc Sort data = SASVACL.Summarized
 217         Out= SASVACL.Sorted Noduprecs;
 218         by Reference_NO cat;
 219         Run;
 
 NOTE: Input data set is already sorted; it has been copied to the output data set.
 NOTE: There were 44215 observations read from the data set SASVACL.SUMMARIZED.
 NOTE: The data set SASVACL.SORTED has 44215 observations and 3 variables.
 NOTE: Compressing data set SASVACL.SORTED increased size by 4.55 percent. 
       Compressed is 23 pages; un-compressed would require 22 pages.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.08 seconds
       user cpu time       0.06 seconds
       system cpu time     0.01 seconds
       memory              621.78k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:28 AM
       Step Count                        19  Switch Count  41
       
 
 220        
 221        /* Transposing to restructure the data and creating dataset Online_Payment */
 222        /* This dataset is further used to map with PF_Dump */
 223        
 224        Proc Transpose data = SASVACL.Sorted out=SASVACL.OnlinePaymt (drop =_Name_);
 225        by Reference_NO;
 226        ID Cat;
 227        Var PhysicalCollection;
 228        run;
 
 NOTE: There were 44215 observations read from the data set SASVACL.SORTED.
 NOTE: The data set SASVACL.ONLINEPAYMT has 44122 observations and 4 variables.
 NOTE: Compressing data set SASVACL.ONLINEPAYMT decreased size by 7.14 percent. 
       Compressed is 26 pages; un-compressed would require 28 pages.
 NOTE: PROCEDURE TRANSPOSE used (Total process time):
       real time           0.18 seconds
       user cpu time       0.12 seconds
       system cpu time     0.03 seconds
       memory              2636.59k
       OS Memory           27740.00k
       Timestamp           11/14/2017 11:39:29 AM
       Step Count                        20  Switch Count  49
       
 
 229        
 230        /* This step is to set all missing values of numer variables to 0  */
 231        /* and create a new column which is used in future */
 232        
 233        Options Compress = Yes;
 234        Data SASVACL.Online_Payment;
 235        set SASVACL.OnlinePaymt;
 236        array change _Numeric_;
 237        do over change;
 238        if change = . then change = 0;
 239        end;
 240        Receipt_AMT = Sum(of _Numeric_);
 241        Run;
 
 NOTE: There were 44122 observations read from the data set SASVACL.ONLINEPAYMT.
 NOTE: The data set SASVACL.ONLINE_PAYMENT has 44122 observations and 5 variables.
 NOTE: Compressing data set SASVACL.ONLINE_PAYMENT decreased size by 30.30 percent. 
       Compressed is 23 pages; un-compressed would require 33 pages.
 NOTE: DATA statement used (Total process time):
       real time           0.07 seconds
       user cpu time       0.04 seconds
       system cpu time     0.01 seconds
       memory              740.84k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:29 AM
       Step Count                        21  Switch Count  55
       
 
 242        
 243        /* Summarizing and Creating dataset DAC_CCS  */
 244        /* This dataset is further used to map with PF_Dump */
 245        
 246        Options Compress = Yes;
 247        Proc Sql;
 248        create table SASVACL.DAC_CCS as
 249        Select Reference_NO, Sum(Physical_Collection) as PhysicalCollection, Sum(Penal_Bounce) as PenalBounce
 250        from SASVACL.Status
 251        where Status = 'Clear' and Flag = 0
 252        Group by Reference_NO;
 NOTE: Compressing data set SASVACL.DAC_CCS decreased size by 12.50 percent. 
       Compressed is 28 pages; un-compressed would require 32 pages.
 NOTE: Table SASVACL.DAC_CCS created, with 63383 rows and 3 columns.
 
 253        Quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.42 seconds
       user cpu time       0.40 seconds
       system cpu time     0.06 seconds
       memory              8266.28k
       OS Memory           32504.00k
       Timestamp           11/14/2017 11:39:29 AM
       Step Count                        22  Switch Count  55
       
 
 254        
 255        
 256        options MPRINT SYMBOLGEN;
 257        data _null_;
 258        CALL SYMPUT('DD' , put(day(today()),BEST12. ));
 259        CALL SYMPUT('TODAY', PUT(today(),DATE9.) );
 V#2260        CALL SYMPUT('MONTHYY', put(today(),MONYY7.));
 261        Run;
 
 NOTE: DATA statement used (Total process time):
       real time           0.01 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              521.65k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:39:29 AM
       Step Count                        23  Switch Count  28
       
 
 262        %PUT &DD ;
 SYMBOLGEN:  Macro variable DD resolves to           14
 14
 263        %PUT &TODAY ;
 SYMBOLGEN:  Macro variable TODAY resolves to 14NOV2017
 14NOV2017
 264        %PUT &MONTHYY ;
 SYMBOLGEN:  Macro variable MONTHYY resolves to NOV2017
 NOV2017
 265        
 266        /* This referes to the source for the PF_Dump which is SAS Dataset */
 267        
 268        libname AMAY "\\\\10.4.1.197\\CollectionAnalysis\\ALLOCATION_&MONTHYY";
 SYMBOLGEN:  Macro variable MONTHYY resolves to NOV2017
 NOTE: Libref AMAY was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: \\\\10.4.1.197\\CollectionAnalysis\\ALLOCATION_NOV2017
 269        
 270        
 271        /* This is Accessing SAS Dataset and Creating new Dataset 'PF_Dump' */
 272        
 273        
 274        Options Compress = Yes;
 275        Proc Sql;
 276        Create table SASVACL.PF_Dump as
 277        Select ALLOCATION,ALLOCATION_DATE,BOM_BKT,AGREEMENTID,AGREEMENTNO,BRANCH,PRODUCTFLAG,
 278             SCHME_DESC,SANCT_DATE,DISB_AMT,DISB_DATE,EMI_START_DATE,NPA_STAGEID,NPA_DATE,EMI_AMT,
 279             EMI_OD_AMT,NO_OF_EMI_OD,BUCKET,DPD,PRINCIPAL_OUTSTANDING,INTEREST_OD,NONSTARTER,
 SYMBOLGEN:  Macro variable MONTHYY resolves to NOV2017280             
             PRODUCT,BIL_Prod_Type,CITY,STATE,REGION,CATEGORY,Priority,segment,CRN,CUSTOMERNAME,
 
 281             Referrer_Name, PENAL_PENDING,BCC_PENDING,(PRINCIPAL_OUTSTANDING/1000000) as POS_MIO,Input(Bucket, 4.) as BM_Bucket,
 282             TODAY() as Current_Date Format Date9.
 283        from Amay.pwcd_&MONTHYY._1
 284        where Allocation NOT IN ('INHOUSE', 'DND') and BOM_BKT NE '0';
 NOTE: Compressing data set SASVACL.PF_DUMP decreased size by 70.12 percent. 
       Compressed is 3390 pages; un-compressed would require 11347 pages.
 NOTE: Table SASVACL.PF_DUMP created, with 930407 rows and 38 columns.
 
 285        Quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           1:30.04
       user cpu time       37.20 seconds
       system cpu time     7.25 seconds
       memory              4549.06k
       OS Memory           29800.00k
       Timestamp           11/14/2017 11:40:59 AM
       Step Count                        24  Switch Count  662
       
 
 286        
 287        
 288        
 289        proc import out = SASVACL.Off_Book_data
 290        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Off_Book_data.xlsx"
 291        DBMS=xlsx REPLACE;
 292        sheet= 'Sheet1';
 293        Getnames=Yes;
 294        run;
 
 NOTE: The import data set has 12767 observations and 3 variables.
 NOTE: Compressing data set SASVACL.OFF_BOOK_DATA increased size by 60.00 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.87 seconds
       user cpu time       0.82 seconds
       system cpu time     0.03 seconds
       memory              729.31k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:41:00 AM
       Step Count                        25  Switch Count  44
       
       Compressed is 8 pages; un-compressed would require 5 pages.
 NOTE: SASVACL.OFF_BOOK_DATA data set was successfully created.
 
 295        
 296        
 297        Data SASVACL.Off_Book_data;
 298        Set SASVACL.Off_Book_data;
 299        Agreement_ID = (Put(Left(AGREEMENTID), $15.));
 300        Run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       299:26   
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       user cpu time       0.03 seconds
       system cpu time     0.01 seconds
       memory              622.93k
       OS Memory           25688.00k
       Timestamp           11/14/2017 11:41:00 AM
       Step Count                        26  Switch Count  56
       
 NOTE: There were 12767 observations read from the data set SASVACL.OFF_BOOK_DATA.
 NOTE: The data set SASVACL.OFF_BOOK_DATA has 12767 observations and 4 variables.
 NOTE: Compressing data set SASVACL.OFF_BOOK_DATA increased size by 12.50 percent. 
       Compressed is 9 pages; un-compressed would require 8 pages.
 
 301        
 302        
 303        Options Compress = Yes;
 304        Proc Sql;
 305        Create table SASVACL.PF_Dump_OFF as
 306        select c.*, case when d.ON_OFF_Book = '' then 'ON-BOOK' else d.ON_OFF_Book end as ONOFF_BOOK
 307        from SASVACL.PF_Dump c Left Join SASVACL.Off_Book_data d
 308        on c.AgreementID = d.Agreement_ID;
 NOTE: Compressing data set SASVACL.PF_DUMP_OFF decreased size by 69.37 percent. 
       Compressed is 3518 pages; un-compressed would require 11487 pages.NOTE: Table SASVACL.PF_DUMP_OFF created, with 930407 rows 
                                                                               and 39 columns.
 
 
 309        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           10.03 seconds
       user cpu time       7.00 seconds
       system cpu time     4.10 seconds
       memory              784599.04k
       OS Memory           808160.00k
       Timestamp           11/14/2017 11:41:10 AM
       Step Count                        27  Switch Count  77
       
 
 310        
 311        
 312        Options Compress = Yes;
 313        Proc Sql;
 314        Create table SASVACL.Del as
 315        Select compress(Appl_ID,'', 'kd')as AgreementID, BUCKET
 316        from Fintest.Delq_View1;
 NOTE: Compressing data set SASVACL.DEL decreased size by 24.29 percent. 
       Compressed is 293 pages; un-compressed would require 387 pages.
 NOTE: Table SASVACL.DEL created, with 788909 rows and 2 columns.
 
 317        Quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           6:01.52
       user cpu time       3.26 seconds
       system cpu time     1.99 seconds
       memory              3422.12k
       OS Memory           29276.00k
       Timestamp           11/14/2017 11:47:12 AM
       Step Count                        28  Switch Count  45
       
 
 318        
 319        /* This is mapping PF_Dump with Deliquency and considering only Bucket variable */
 320        
 321        Options Compress = Yes;
 322        Proc Sql;
 323        Create table SASVACL.PF_Del as
 324        select c.*, case when d.Bucket =. then 0 else d.bucket end as Current_Bkt
 325        from SASVACL.PF_Dump_OFF c Left Join SASVACL.Del d
 326        on c.AgreementID = d.AgreementID;
 NOTE: Compressing data set SASVACL.PF_DEL decreased size by 68.92 percent. 
       Compressed is 3570 pages; un-compressed would require 11487 pages.
 NOTE: Table SASVACL.PF_DEL created, with 930407 rows and 40 columns.
 
 327        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           7.91 seconds
       user cpu time       7.42 seconds
       system cpu time     0.99 seconds
       memory              57106.59k
       OS Memory           80964.00k
       Timestamp           11/14/2017 11:47:20 AM
       Step Count                        29  Switch Count  113
       
 
 328        
 329        
 330        /* Creating New Column on Various Conditions applied */
 331        
 332        Options Compress = Yes;
 333        Data SASVACL.SysSummary;
 334        set SASVACL.Pf_Del;
 335        Length System_Summary $15;
 336        Format System_Summary $ 15.;
 337        if Current_Bkt = 0 then System_Summary = 'NORMALISED';
 338        Else if Current_Bkt=1 and BM_Bucket>1 then System_Summary = 'OUT OFF30+';
 339        Else if Current_Bkt= BM_Bucket then System_Summary = 'STAB';
 340        Else if BM_Bucket > Current_Bkt then System_Summary = 'ROLL BACK';
 341        Else if Current_Bkt > BM_Bucket then System_Summary = 'ROLL FORWARD';
 342        
 343        MOB = INTCK('Month', EMI_START_DATE, Current_Date);
 344        If  Product = 'X-SELL' and Referrer_Name IN('PLATDIRECT', 'PLATONLINE', 'PLATTAB', 'PLATPOS'
 345                      'PLATSIS') Then Sub_Product = 'PLPOS'; Else Sub_Product = PRODUCTFLAG;
 346        
 347        run;
 
 NOTE: DATA statement used (Total process time):
       real time           6.78 seconds
       user cpu time       5.86 seconds
       system cpu time     0.84 seconds
       memory              892.93k
       OS Memory           26200.00k
       Timestamp           11/14/2017 11:47:27 AM
       Step Count                        30  Switch Count  139
       
 NOTE: There were 930407 observations read from the data set SASVACL.PF_DEL.
 NOTE: The data set SASVACL.SYSSUMMARY has 930407 observations and 43 variables.
 NOTE: Compressing data set SASVACL.SYSSUMMARY decreased size by 67.68 percent. 
       Compressed is 3856 pages; un-compressed would require 11929 pages.
 
 348        
 349        
 350        /* Mapping the Above dataset with Previously created DAC_CCS */
 351        
 352        Options Compress = Yes;
 353        Proc Sql;
 354        Create table SASVACL.PFDACCCS as
 355        select c.*, case when d.PhysicalCollection =. then 0 else d.PhysicalCollection end as DAC,
 356        case when d.PenalBounce =. then 0 else d.PenalBounce end as Penal_Bounce
 357        from SASVACL.SysSummary c Left Join SASVACL.DAC_CCS d
 358        on c.AgreementID = d.Reference_NO;
 NOTE: Compressing data set SASVACL.PFDACCCS decreased size by 67.82 percent. 
       Compressed is 3889 pages; un-compressed would require 12084 pages.
 NOTE: Table SASVACL.PFDACCCS created, with 930407 rows and 45 columns.
 
 359        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           14.20 seconds
       user cpu time       8.12 seconds
       system cpu time     4.02 seconds
       memory              826032.89k
       OS Memory           850072.00k
       Timestamp           11/14/2017 11:47:41 AM
       Step Count                        31  Switch Count  129
       
 
 360        
 361        /* Mapping the Above dataset with Previously created Online_Payment to getReceipt_Amt */
 362        
 363        Options Compress = Yes;
 364        Proc Sql;
 365        Create table SASVACL.PFOnlinePayment as
 366        select c.*, d.*
 367        from  SASVACL.PFDACCCS c Left Join SASVACL.Online_Payment d
 368        on c.AgreementID = d.Reference_NO;
 NOTE: Compressing data set SASVACL.PFONLINEPAYMENT decreased size by 67.42 percent. 
       Compressed is 4153 pages; un-compressed would require 12746 pages.
 NOTE: Table SASVACL.PFONLINEPAYMENT created, with 930407 rows and 50 columns.
 
 369        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           8.08 seconds
       user cpu time       5.92 seconds
       system cpu time     1.29 seconds
       memory              8891.25k
       OS Memory           33188.00k
       Timestamp           11/14/2017 11:47:49 AM
       Step Count                        32  Switch Count  109
       
 
 370        
 371        
 372        /* Accesing TPSL_View and creating new SASDataset */
 373        
 374        Options Compress = Yes;
 375        Proc Sql;
 376        Create table SASVACL.TPSL  as
 377        Select *
 378        from FINRL.TPSL_View;
 NOTE: Compressing data set SASVACL.TPSL decreased size by 93.37 percent. 
       Compressed is 3193 pages; un-compressed would require 48179 pages.NOTE: Table SASVACL.TPSL created, with 2938877 rows and 11 
                                                                               columns.
 
 
 379        Quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           3:42.58
       user cpu time       29.06 seconds
       system cpu time     8.84 seconds
       memory              3208.18k
       OS Memory           29272.00k
       Timestamp           11/14/2017 11:51:32 AM
       Step Count                        33  Switch Count  48
       
 
 380        
 381        Proc Sort Data = SASVACL.TPSL (KEEP = AgreementID UPLOADED_DATE STATUS);
 382        By AgreementID DESCENDING UPLOADED_DATE;
 383        run;
 NOTE: There were 2938877 observations read from the data set SASVACL.TPSL.
 NOTE: The data set SASVACL.TPSL has 2938877 observations and 3 variables.
 NOTE: Compressing data set SASVACL.TPSL increased size by 21.19 percent. 
       Compressed is 1693 pages; un-compressed would require 1397 pages.
 
 NOTE: PROCEDURE SORT used (Total process time):
       real time           4.23 seconds
       user cpu time       5.92 seconds
       system cpu time     1.12 seconds
       memory              257609.46k
       OS Memory           282224.00k
       Timestamp           11/14/2017 11:51:36 AM
       Step Count                        34  Switch Count  74
       
 
 384        
 385        Options Compress = Yes;
 386        Data SASVACL.TPSL_FINAL;
 387        SET SASVACL.TPSL;
 388        by AgreementID;
 389        If First.AgreementID;
 390        run;
 
 NOTE: DATA statement used (Total process time):
       real time           3.62 seconds
       user cpu time       2.82 seconds
       system cpu time     0.56 seconds
       memory              795.78k
       OS Memory           26456.00k
       Timestamp           11/14/2017 11:51:39 AM
       Step Count                        35  Switch Count  75
       
 NOTE: There were 2938877 observations read from the data set SASVACL.TPSL.
 NOTE: The data set SASVACL.TPSL_FINAL has 2467693 observations and 3 variables.
 NOTE: Compressing data set SASVACL.TPSL_FINAL increased size by 21.31 percent. 
       Compressed is 1423 pages; un-compressed would require 1173 pages.
 
 391        
 392        
 393        /* Mapping the Main Dataset with TPSL  */
 394        
 395        Options Compress = Yes;
 396        Proc Sql;
 397        Create table SASVACL.PFTPS as
 398        select c.*,. format =10. as Presentation, d.Status as Status_Tpsl
 399        from SASVACL.PFOnlinePayment c Left Join SASVACL.TPSL_FINAL d
 400        on c.AgreementID = d.AgreementID;
 NOTE: Compressing data set SASVACL.PFTPS decreased size by 67.39 percent. 
       Compressed is 4273 pages; un-compressed would require 13105 pages.
 NOTE: Table SASVACL.PFTPS created, with 930407 rows and 52 columns.
 
 401        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           13.41 seconds
       user cpu time       9.45 seconds
       system cpu time     1.80 seconds
       memory              130948.00k
       OS Memory           155208.00k
       Timestamp           11/14/2017 11:51:53 AM
       Step Count                        36  Switch Count  141
       
 
 402        
 403        
 404        /* Apllying condition only after joining since we are creating a column
 405        Presentation in previous step*/
 406        
 407        Options Compress = Yes;
 408        Data SASVACL.PFTPS;
 409        set SASVACL.PFTPS;
 410        If Status_Tpsl = 'CLR' or Status_Tpsl = 'Clr'  then Presentation = EMI_AMt;
 411        Else Presentation = Presentation;
 412        run;
 
 NOTE: DATA statement used (Total process time):
       real time           7.96 seconds
       user cpu time       6.36 seconds
       system cpu time     1.15 seconds
       memory              899.21k
       OS Memory           26456.00k
       Timestamp           11/14/2017 11:52:01 AM
       Step Count                        37  Switch Count  107
       
 NOTE: There were 930407 observations read from the data set SASVACL.PFTPS.
 NOTE: The data set SASVACL.PFTPS has 930407 observations and 52 variables.
 NOTE: Compressing data set SASVACL.PFTPS decreased size by 67.39 percent. 
       Compressed is 4273 pages; un-compressed would require 13105 pages.
 
 413        
 414        
 415        proc import out = SASVACL.Special_Presentation
 416        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Special_Presentation.xlsx"
 417        DBMS=xlsx REPLACE;
 418        sheet= 'SP';
 419        Getnames=Yes;
 420        run;
 
 NOTE: The import data set has 103262 observations and 2 variables.
 NOTE: Compressing data set SASVACL.SPECIAL_PRESENTATION increased size by 38.46 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           3.61 seconds
       user cpu time       3.43 seconds
       system cpu time     0.01 seconds
       memory              722.46k
       OS Memory           26456.00k
       Timestamp           11/14/2017 11:52:04 AM
       Step Count                        38  Switch Count  44
       
       Compressed is 36 pages; un-compressed would require 26 pages.
 NOTE: SASVACL.SPECIAL_PRESENTATION data set was successfully created.
 
 421        
 422        Data SASVACL.Special_Presentation;
 423        Set SASVACL.Special_Presentation;
 424        Agreement_ID = (Put(Left(Agreement_NO), $15.));
 425        Run;
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       424:26   
 NOTE: DATA statement used (Total process time):
       real time           0.17 seconds
       user cpu time       0.10 seconds
       system cpu time     0.04 seconds
       memory              880.96k
       OS Memory           26200.00k
       Timestamp           11/14/2017 11:52:05 AM
       Step Count                        39  Switch Count  42
       
 NOTE: There were 103262 observations read from the data set SASVACL.SPECIAL_PRESENTATION.
 NOTE: The data set SASVACL.SPECIAL_PRESENTATION has 103262 observations and 3 variables.
 NOTE: Compressing data set SASVACL.SPECIAL_PRESENTATION decreased size by 21.57 percent. 
       Compressed is 40 pages; un-compressed would require 51 pages.
 
 426        
 427        /* Mapping the main Dataset with Special Presentation */
 428        
 429        Options Compress = Yes;
 430        Proc Sql;
 431        Create table SASVACL.PF_SP as
 432        select c.*, d.Agreement_No as AgreementID, d.Amt as SpecialPresentation
 433        from SASVACL.PFTPS c Left Join SASVACL.Special_Presentation d
 434        on c.AgreementID = d.Agreement_ID;
 WARNING: Variable AgreementID already exists on file SASVACL.PF_SP.
 NOTE: Compressing data set SASVACL.PF_SP decreased size by 67.32 percent. 
       Compressed is 4344 pages; un-compressed would require 13292 pages.NOTE: Table SASVACL.PF_SP created, with 930407 rows and 53 
                                                                               columns.
 
 
 435        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           12.75 seconds
       user cpu time       8.47 seconds
       system cpu time     4.35 seconds
       memory              903798.29k
       OS Memory           927812.00k
       Timestamp           11/14/2017 11:52:17 AM
       Step Count                        40  Switch Count  91
       
 
 436        
 437        proc import out = SASVACL.Recovery_Discrepancy
 438        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Recovery_Discrepancy.xlsx"
 439        DBMS=xlsx REPLACE;
 440        sheet= 'RP';
 441        Getnames=Yes;
 442        run;
 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.07 seconds
       user cpu time       0.03 seconds
       system cpu time     0.03 seconds
 NOTE: The import data set has 1 observations and 2 variables.
 NOTE: Compressing data set SASVACL.RECOVERY_DISCREPANCY increased size by 100.00 percent. 
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.RECOVERY_DISCREPANCY data set was successfully created.
       memory              335.93k
       OS Memory           26456.00k
       Timestamp           11/14/2017 11:52:18 AM
       Step Count                        41  Switch Count  37
       
 
 443        
 444        Data SASVACL.Recovery_Discrepancy;
 445        Set SASVACL.Recovery_Discrepancy;
 446        Agreement_ID = (Put(Left(Agreement_NO), $15.));
 447        Run;
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       446:26   
 NOTE: There were 1 observations read from the data set SASVACL.RECOVERY_DISCREPANCY.
 NOTE: The data set SASVACL.RECOVERY_DISCREPANCY has 1 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.04 seconds
       user cpu time       0.01 seconds
       system cpu time     0.03 seconds
       memory              752.71k
       OS Memory           26456.00k
       Timestamp           11/14/2017 11:52:18 AM
 NOTE: Compressing data set SASVACL.RECOVERY_DISCREPANCY increased size by 100.00 percent. 
       Compressed is 2 pages; un-compressed would require 1 pages.
       Step Count                        42  Switch Count  30
       
 
 448        
 449        /* Mapping the main Dataset with Special Presentation */
 450        
 451        Options Compress = Yes;
 452        Proc Sql;
 453        Create table SASVACL.PF_RecoveryDiscrepancy as
 454        select c.*, d.Agreement_No as AgreementID, d.Amt as Discrepancy
 455        from SASVACL.PF_SP  c Left Join SASVACL.Recovery_Discrepancy d
 456        on c.AgreementID = d.Agreement_ID;
 WARNING: Variable AgreementID already exists on file SASVACL.PF_RECOVERYDISCREPANCY.
 NOTE: Compressing data set SASVACL.PF_RECOVERYDISCREPANCY decreased size by 67.25 percent. 
       Compressed is 4416 pages; un-compressed would require 13485 pages.
 NOTE: Table SASVACL.PF_RECOVERYDISCREPANCY created, with 930407 rows and 54 columns.
 
 457        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           8.33 seconds
       user cpu time       6.94 seconds
       system cpu time     1.31 seconds
       memory              3957.09k
       OS Memory           29532.00k
       Timestamp           11/14/2017 11:52:26 AM
       Step Count                        43  Switch Count  132
       
 
 458        
 459        /* Importing Additional_Payment Excel File */
 460        
 461        proc import out = SASVACL.Additional_Payment
 462        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Additional_Payment.xlsx"
 463        DBMS=xlsx REPLACE;
 464        sheet= 'AP';
 465        Getnames=Yes;
 466        run;
 
 NOTE: The import data set has 15949 observations and 2 variables.
 NOTE: Compressing data set SASVACL.ADDITIONAL_PAYMENT increased size by 75.00 percent. 
       Compressed is 7 pages; un-compressed would require 4 pages.
 NOTE: SASVACL.ADDITIONAL_PAYMENT data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.60 seconds
       user cpu time       0.51 seconds
       system cpu time     0.01 seconds
       memory              726.06k
       OS Memory           26712.00k
       Timestamp           11/14/2017 11:52:27 AM
       Step Count                        44  Switch Count  48
       
 
 467        
 468        Data SASVACL.Additional_Payment;
 469        Set SASVACL.Additional_Payment;
 470        Agreement_ID = (Put(Left(Agreement_NO), $15.));
 471        Run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       470:26   
 NOTE: There were 15949 observations read from the data set SASVACL.ADDITIONAL_PAYMENT.
 NOTE: The data set SASVACL.ADDITIONAL_PAYMENT has 15949 observations and 3 variables.
 NOTE: Compressing data set SASVACL.ADDITIONAL_PAYMENT decreased size by 12.50 percent. 
       Compressed is 7 pages; un-compressed would require 8 pages.
 NOTE: DATA statement used (Total process time):
       real time           0.05 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
       memory              735.21k
       OS Memory           26712.00k
       Timestamp           11/14/2017 11:52:27 AM
       Step Count                        45  Switch Count  47
       
 
 472        
 473        
 474        /* Mapping the main Dataset with Additional_Payment */
 475        
 476        Options Compress = Yes;
 477        Proc Sql;
 478        Create table SASVACL.PF_AdditionaPayment as
 479        select c.*, d.Agreement_ID as AgreementID, d.Amt as AdditionalPayment
 480        from SASVACL.PF_RecoveryDiscrepancy  c Left Join SASVACL.Additional_Payment d
 481        on c.AgreementID = d.Agreement_ID;
 WARNING: Variable AgreementID already exists on file SASVACL.PF_ADDITIONAPAYMENT.
 NOTE: Compressing data set SASVACL.PF_ADDITIONAPAYMENT decreased size by 66.73 percent. 
       Compressed is 4487 pages; un-compressed would require 13485 pages.NOTE: Table SASVACL.PF_ADDITIONAPAYMENT created, with 
                                                                               930407 rows and 55 columns.
 
 
 482        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           8.35 seconds
       user cpu time       6.30 seconds
       system cpu time     1.38 seconds
       memory              5546.00k
       OS Memory           29852.00k
       Timestamp           11/14/2017 11:52:35 AM
       Step Count                        46  Switch Count  119
       
 
 483        
 484        
 485        /* Applying Various Conditions creating new columns  */
 486        
 487        Options Compress = Yes;
 488        Data SASVACL.PF_AdditionaPayment;
 489        set SASVACL.PF_AdditionaPayment ;
 490        Length Dac_Summary $ 15;
 491        array change DAC Penal_Bounce Receipt_AMT Presentation SpecialPresentation
 492        AdditionalPayment Discrepancy ;
 493        do over change;
 494        if change = . then change = 0;
 495        end;
 496        
 497        Total_Collection = DAC + Penal_Bounce + Receipt_AMT + Presentation + Discrepancy + SpecialPresentation +
 497      ! AdditionalPayment  ;
 498        EMI_Collected = INT(Total_Collection/EMI_AMT);
 499        Peak_Bucket = BM_Bucket+1;
 500        
 501        If (Peak_Bucket - EMI_Collected) <0 then As_ON_Date_Bucket =0;
 502          Else As_ON_Date_Bucket = (Peak_Bucket - EMI_Collected);
 503        If NPA_StageID= 'WRITEOFF' then DAC_Summary = 'C-OFF';
 504        Else if As_ON_Date_Bucket = 0 then DAC_Summary = 'NORMALISED';
 505        Else if As_ON_Date_Bucket = 1 and BM_Bucket > 1 then DAC_Summary = 'OUT OFF 30+' ;
 506        Else if As_ON_Date_Bucket = BM_Bucket  then DAC_Summary = 'STAB';
 507        Else if As_ON_Date_Bucket > BM_Bucket then DAC_Summary = 'ROLL FORWARD';
 508        Else if As_ON_Date_Bucket < BM_Bucket then DAC_Summary = 'ROLL BACK';
 509        
 510        If As_ON_Date_Bucket >= Current_Bkt Then Min_Bucket = Current_Bkt;
 511        Else if As_ON_Date_Bucket < Current_Bkt  then Min_Bucket = As_ON_Date_Bucket ;
 512        run;
 
 NOTE: There were 930407 observations read from the data set SASVACL.PF_ADDITIONAPAYMENT.
 NOTE: The data set SASVACL.PF_ADDITIONAPAYMENT has 930407 observations and 61 variables.
 NOTE: Compressing data set SASVACL.PF_ADDITIONAPAYMENT decreased size by 67.93 percent. 
       Compressed is 4521 pages; un-compressed would require 14098 pages.
 NOTE: DATA statement used (Total process time):
       real time           9.30 seconds
       user cpu time       6.92 seconds
       system cpu time     1.43 seconds
       memory              1108.25k
       OS Memory           26712.00k
       Timestamp           11/14/2017 11:52:44 AM
       Step Count                        47  Switch Count  167
       
 
 513        
 514        
 515        /* Applying Various Conditions creating new columns  */
 516        
 517        Options Compress = Yes;
 518        Data SASVACL.PF_Final_Summary;
 519        set SASVACL.PF_AdditionaPayment ;
 520         Length Final_Summary $ 12;
 521        If NPA_StageID= 'WRITEOFF' then Final_Summary = 'C-OFF';
 522        Else if Min_Bucket  = 0 then Final_Summary = 'NORMALISED';
 523        Else if Min_Bucket  = 1 and BM_Bucket > 1 then Final_Summary = 'OUT OFF 30+' ;
 524        Else if Min_Bucket  = BM_Bucket  then Final_Summary = 'STAB';
 525        Else if Min_Bucket  > BM_Bucket then Final_Summary = 'ROLL FORWARD';
 526        Else if Min_Bucket  < BM_Bucket then Final_Summary = 'ROLL BACK';
 527        Else Final_Summary = Final_Summary;
 528        
 529        
 530        IF NPA_STAGEID = 'WRITEOFF' and Total_Collection > 0 then Recovery = 1; else Recovery = 0;
 531        
 532        IF NPA_STAGEID = 'WRITEOFF' and DAC_Summary ^= 'ROLL FORWARD' and Receipt_AMT > 0 then
 533        PhysicalCollection_POS = POS_MIO;
 534        
 535        IF NPA_STAGEID = 'WRITEOFF' and DAC_Summary ^= 'ROLL FORWARD' and DAC > 0 and
 536        PhysicalCollection_POS = . then PhysicalCollection_POS = POS_MIO;
 537        
 538        
 539        IF NPA_STAGEID = 'WRITEOFF' and DAC_Summary ^= 'ROLL FORWARD' and Presentation > 0
 540        Then SystemCollection_POS = POS_MIO;
 541        
 542        run;
 
 NOTE: There were 930407 observations read from the data set SASVACL.PF_ADDITIONAPAYMENT.
 NOTE: The data set SASVACL.PF_FINAL_SUMMARY has 930407 observations and 65 variables.
 NOTE: Compressing data set SASVACL.PF_FINAL_SUMMARY decreased size by 67.42 percent. 
       Compressed is 4812 pages; un-compressed would require 14769 pages.
 NOTE: DATA statement used (Total process time):
       real time           13.70 seconds
       user cpu time       5.99 seconds
       system cpu time     1.48 seconds
       memory              848.18k
       OS Memory           26712.00k
       Timestamp           11/14/2017 11:52:58 AM
       Step Count                        48  Switch Count  166
       
 
 543        
 544        /* Importing AllocationGap File  */
 545        
 546        Options Validvarname= Any;
 547        proc import out = SASVACL.Allocation_GAP_Ecollect (Rename= (AGRMNTID = AgreementID
 548        'Agency Code'n = Agency_Code  'Agency Name'n = Agency_Name))
 549        datafile= "\\\\10.4.1.162\\ecollect\\1_Gap_Report\\Allocation_GAP.xlsx"
 550        DBMS=xlsx REPLACE;
 551        sheet= 'Data';
 552        Getnames=Yes;
 553        run;
 
 NOTE: The import data set has 900691 observations and 20 variables.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           5:06.12
       user cpu time       4:42.40
       system cpu time     2.27 seconds
       memory              3341.06k
       OS Memory           28248.00k
       Timestamp           11/14/2017 11:58:04 AM
       Step Count                        49  Switch Count  47
       
 NOTE: Compressing data set SASVACL.ALLOCATION_GAP_ECOLLECT decreased size by 48.68 percent. 
       Compressed is 2383 pages; un-compressed would require 4643 pages.
 NOTE: SASVACL.ALLOCATION_GAP_ECOLLECT data set was successfully created.
 
 554        
 555        /* Converting Numeric Variable to Character Variable */
 556        
 557        Data SASVACL.Allocation_GAP_Ecollect;
 558        Length Agency_Code  $20;
 559        Set SASVACL.Allocation_GAP_Ecollect;
 560        Format Agency_Code $ 20.;
 561        Agreement_ID = (Put(Left(AgreementID), $15.));
 562        Run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       561:26   
 NOTE: DATA statement used (Total process time):
       real time           3.94 seconds
       user cpu time       2.96 seconds
       system cpu time     0.82 seconds
       memory              821.53k
       OS Memory           26712.00k
       Timestamp           11/14/2017 11:58:08 AM
       Step Count                        50  Switch Count  75
       
 NOTE: There were 900691 observations read from the data set SASVACL.ALLOCATION_GAP_ECOLLECT.
 NOTE: The data set SASVACL.ALLOCATION_GAP_ECOLLECT has 900691 observations and 21 variables.
 NOTE: Compressing data set SASVACL.ALLOCATION_GAP_ECOLLECT decreased size by 50.58 percent. 
       Compressed is 2515 pages; un-compressed would require 5089 pages.
 
 563        
 564        
 565        /* Mapping Main dataset with Allocation Gap*/
 566        
 567        Options Compress = Yes;
 568        Proc Sql;
 569        Create table SASVACL.PF_AllocationGap as
 570        select c.*, d.Agency_Code , d.Agency_Name
 571        from SASVACL.PF_Final_Summary c Left Join SASVACL.Allocation_GAP_Ecollect d
 572        on c.AgreementID = d.Agreement_ID;
 NOTE: Compressing data set SASVACL.PF_ALLOCATIONGAP decreased size by 66.29 percent. 
       Compressed is 5227 pages; un-compressed would require 15507 pages.NOTE: Table SASVACL.PF_ALLOCATIONGAP created, with 930407 
                                                                               rows and 67 columns.
 
 
 573        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           17.95 seconds
       user cpu time       11.98 seconds
       system cpu time     5.50 seconds
       memory              1020342.79k
       OS Memory           1044704.00k
       Timestamp           11/14/2017 11:58:26 AM
       Step Count                        51  Switch Count  101
       
 
 574        
 575        /* Importing TrailGap File  */
 576        
 577        proc import out = SASVACL.Trail_GAP_Ecollect (Rename= ('Agreement ID'n = AgreementID
 578        'Trail Status'n = Trail_Status) )
 579        datafile= "\\\\10.4.1.162\\ecollect\\1_Gap_Report\\Trail_GAP.xlsx"
 580        DBMS=xlsx REPLACE;
 581        sheet= 'Data';
 582        Getnames=Yes;
 583        run;
 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           6:00.11
 NOTE: The import data set has 882681 observations and 26 variables.
 NOTE: Compressing data set SASVACL.TRAIL_GAP_ECOLLECT decreased size by 51.27 percent. 
       Compressed is 3163 pages; un-compressed would require 6491 pages.
 NOTE: SASVACL.TRAIL_GAP_ECOLLECT data set was successfully created.
       user cpu time       5:28.22
       system cpu time     3.26 seconds
       memory              66593.71k
       OS Memory           100148.00k
       Timestamp           11/14/2017 12:04:26 PM
       Step Count                        52  Switch Count  43
       
 
 584        
 585        /* Converting Numeric Variable to Character Variable */
 586        
 587        Data SASVACL.Trail_GAP_Ecollect;
 588        Set SASVACL.Trail_GAP_Ecollect;
 V$0V$1
 590        Run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       589:26   
 NOTE: There were 882681 observations read from the data set SASVACL.TRAIL_GAP_ECOLLECT.
 NOTE: The data set SASVACL.TRAIL_GAP_ECOLLECT has 882681 observations and 27 variables.
 NOTE: Compressing data set SASVACL.TRAIL_GAP_ECOLLECT decreased size by 50.94 percent. 
       Compressed is 3281 pages; un-compressed would require 6688 pages.
 NOTE: DATA statement used (Total process time):
       real time           5.98 seconds
       user cpu time       3.96 seconds
       system cpu time     0.63 seconds
       memory              727.18k
       OS Memory           27736.00k
       Timestamp           11/14/2017 12:04:32 PM
       Step Count                        53  Switch Count  91
       
 
 591        
 592        /* Mapping Main dataset with TrailGap*/
 593        
 594        Options Compress = Yes;
 595        Proc Sql;
 596        Create table SASVACL.PF_TrailGap as
 597        select c.*, d.Trail_Status
 598        from SASVACL.PF_AllocationGap c Left Join SASVACL.Trail_GAP_Ecollect d
 599        on c.AgreementID = d.Agreement_ID;
 NOTE: Compressing data set SASVACL.PF_TRAILGAP decreased size by 65.91 percent. 
       Compressed is 5376 pages; un-compressed would require 15770 pages.
 NOTE: Table SASVACL.PF_TRAILGAP created, with 930407 rows and 68 columns.
 
 600        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           14.03 seconds
       user cpu time       10.10 seconds
       system cpu time     2.01 seconds
       memory              57392.21k
       OS Memory           82792.00k
       Timestamp           11/14/2017 12:04:46 PM
       Step Count                        54  Switch Count  157
       
 
 601        
 602        /* Applying Various Conditions creating new columns  */
 603        
 604        Options Compress = Yes;
 605        Data SASVACL.PF_Claculations;
 606        set SASVACL.PF_TrailGap;
 607        
 608        
 609        If Final_Summary = 'ROLL FORWARD' then Roll_Forward = 1;
 610        Else Roll_Forward = 0;
 611        
 612        
 613        If Trail_Status = 'GAP' then GAP = 1; Else GAP = 0;
 614        
 615        Count = 1;
 616        
 617        Concatenate_Map = Cats(Region, City, Agency_Code, Agency_Name);
 618        
 619        
 620        If Substr(Agency_Name,1,7)= 'INHOUSE' then
 621        do;
 622        Agency_Name ='INHOUSE';
 623        Agency_Code ='INHOUSE';
 624        end;
 625        Else If Substr(Agency_Name,1,7)= '' then
 626        
 627        do;
 628        Agency_Name = 'UNALLOCATED';
 629        Agency_Code = 'UNALLOCATED';
 630        End;
 631        
 632        
 633        Run;
 
 NOTE: DATA statement used (Total process time):
       real time           10.15 seconds
       user cpu time       8.61 seconds
       system cpu time     1.51 seconds
       memory              959.78k
       OS Memory           27736.00k
       Timestamp           11/14/2017 12:04:57 PM
       Step Count                        55  Switch Count  222
       
 NOTE: There were 930407 observations read from the data set SASVACL.PF_TRAILGAP.
 NOTE: The data set SASVACL.PF_CLACULATIONS has 930407 observations and 72 variables.
 NOTE: Compressing data set SASVACL.PF_CLACULATIONS decreased size by 68.02 percent. 
       Compressed is 6073 pages; un-compressed would require 18989 pages.
 
 634        
 635        
 636        
 637        /* Applying Various Conditions creating new columns  */
 638        
 639        Options Compress = Yes;
 640        Data SASVACL.PF_Claculations1;
 641        Set SASVACL.PF_Claculations;
 642        If Recovery = 1 then
 643        do;
 644        POS_Recovery_Cash = Receipt_Amt/1000000;
 645        POS_Recovery_DAC = DAC/1000000;
 646        Bounce_Late_Recovery = Penal_Bounce/1000000;
 647        Presentation_Clearance = Presentation/1000000;
 648        Additional_Payment = AdditionalPayment/1000000;
 649        End;
 650        
 651        array change POS_Recovery_Cash POS_Recovery_DAC Bounce_Late_Recovery Presentation_Clearance
 652        AdditionalPayment Discrepancy Additional_Payment ;
 653        do over change;
 654        if change = . then change = 0;
 655        end;
 656        
 657        If Recovery =1 and DAC>0 then DAC_Recovery_Tag =1;
 658        Else DAC_Recovery_Tag =0;
 659        
 660        If Recovery =1 and Receipt_Amt>0 then Cash_Recovery_Tag =1;
 661        Else Cash_Recovery_Tag =0;
 662        
 663        If Recovery =1 and Presentation>0 then Presentation_Recovery_Tag =1;
 664        Else Presentation_Recovery_Tag =0;
 665        
 666        If Recovery =1 and Penal_Bounce>0 then Bounce_Recovery_Tag =1;
 667        Else Bounce_Recovery_Tag =0;
 668        
 669        Concat_Recovery = Cat(DAC_Recovery_Tag, Cash_Recovery_Tag, Presentation_Recovery_Tag, Bounce_Recovery_Tag);
 670        
 671        If POS_Recovery_DAC = 0 then POS_Recovery_DAC = POS_Recovery_Cash;
 672        
 673        Total_Amt_Recovered_Ex_Penal = Total_Collection - Penal_Bounce;
 674        
 675        Total_Amt_Recovered_MIO = Total_Collection / 1000000;
 676        
 677        If Divide(TOTAL_Amt_Recovered_MIO,POS_MIO) = . then Recovery_Percentage =0;
 678        Else Recovery_Percentage = Divide(TOTAL_Amt_Recovered_MIO,POS_MIO);
 679        
 680        
 681        Run;
 
 NOTE: There were 930407 observations read from the data set SASVACL.PF_CLACULATIONS.
 NOTE: The data set SASVACL.PF_CLACULATIONS1 has 930407 observations and 85 variables.
 
 NOTE: Compressing data set SASVACL.PF_CLACULATIONS1 decreased size by 73.25 percent. 
       Compressed is 6223 pages; un-compressed would require 23261 pages.
 NOTE: DATA statement used (Total process time):
       real time           11.65 seconds
       user cpu time       9.75 seconds
       system cpu time     1.68 seconds
       memory              997.21k
       OS Memory           27736.00k
       Timestamp           11/14/2017 12:05:08 PM
       Step Count                        56  Switch Count  287
       
 
 682        
 683        
 684        
 685        /* Importing Recovery_Mode File  */
 686        
 687        proc import out = SASVACL.Recovery_Mode
 688        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Recovery_Mode.xlsx"
 689        DBMS=xlsx REPLACE;
 690        sheet= 'Sheet2';
 691        Getnames=Yes;
 692        run;
 
 NOTE: The import data set has 15 observations and 6 variables.
 NOTE: Compressing data set SASVACL.RECOVERY_MODE increased size by 100.00 percent. 
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.07 seconds
       user cpu time       0.01 seconds
       system cpu time     0.03 seconds
       memory              905.87k
 NOTE: SASVACL.RECOVERY_MODE data set was successfully created.
       OS Memory           27736.00k
       Timestamp           11/14/2017 12:05:08 PM
       Step Count                        57  Switch Count  49
       
 
 693        
 694        /* Mapping MainDataset with Recovery_Mode tobring Recovery  */
 695        
 696        Proc Sql;
 697        Create table SASVACL.PF_RecoveryMode as
 698        select c.*, d.Recovery_Mode
 699        from SASVACL.PF_Claculations1 c Left Join SASVACL.Recovery_Mode d
 700        on c.Concat_Recovery = d.Concat;
 NOTE: Compressing data set SASVACL.PF_RECOVERYMODE decreased size by 73.57 percent. 
       Compressed is 5856 pages; un-compressed would require 22153 pages.NOTE: Table SASVACL.PF_RECOVERYMODE created, with 930407 
                                                                               rows and 86 columns.
 
 
 701        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           29.99 seconds
       user cpu time       13.54 seconds
       system cpu time     10.26 seconds
       memory              1053323.28k
       OS Memory           1078604.00k
       Timestamp           11/14/2017 12:05:38 PM
       Step Count                        58  Switch Count  86
       
 
 702        
 703        /* Importing Scheme Description file */
 704        
 705        Options Validvarname=any;
 706        
 707        proc import out = SASVACL.Scheme_description (Rename = ('Final Product'n = Final_Product))
 708        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Scheme_description.xlsx"
 709        DBMS=xlsx REPLACE;
 710        sheet= 'Sheet1';
 711        Getnames=Yes;
 712        run;
 
 NOTE: The import data set has 207 observations and 4 variables.
 NOTE: Compressing data set SASVACL.SCHEME_DESCRIPTION increased size by 100.00 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.09 seconds
       user cpu time       0.03 seconds
       system cpu time     0.03 seconds
       memory              924.81k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:05:38 PM
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.SCHEME_DESCRIPTION data set was successfully created.
       Step Count                        59  Switch Count  43
       
 
 713        
 714        /* Mapping MainDataset with SchemeDescription to get FinalProduct  */
 715        
 716        Options Compress = Yes;
 717        Proc Sql;
 718        Create table SASVACL.PF_SchemeDesc as
 719        select c.*, d.Final_Product, d.HFC_CFL
 720        from SASVACL.PF_RecoveryMode c Left Join SASVACL.Scheme_description d
 721        on c.SCHME_DESC = d.SCHME_DESC;
 NOTE: Compressing data set SASVACL.PF_SCHEMEDESC decreased size by 73.56 percent. 
       Compressed is 5857 pages; un-compressed would require 22153 pages.
 NOTE: Table SASVACL.PF_SCHEMEDESC created, with 930407 rows and 88 columns.
 
 722        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           24.98 seconds
       user cpu time       13.90 seconds
       system cpu time     10.46 seconds
       memory              1048410.93k
       OS Memory           1073268.00k
       Timestamp           11/14/2017 12:06:03 PM
       Step Count                        60  Switch Count  92
       
 
 723        
 724        /* Importing NCLProvisionNorms file */
 725        
 726        proc import out = SASVACL.NCL_Provision_Norms
 727        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\NCL_Provision_Norms.xlsx"
 728        DBMS=xlsx REPLACE;
 729        sheet= 'Sheet1';
 730        Getnames=Yes;
 731        run;
 
 NOTE: The import data set has 104 observations and 5 variables.
 NOTE: Compressing data set SASVACL.NCL_PROVISION_NORMS increased size by 100.00 percent. NOTE: SASVACL.NCL_PROVISION_NORMS data set 
                                                                                                was successfully created.
       Compressed is 2 pages; un-compressed would require 1 pages.
 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.08 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
       memory              654.12k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:06:04 PM
       Step Count                        61  Switch Count  45
       
 NOTE: SASVACL.NCL_PROVISION_NORMS data set was successfully created.
 
 732        
 733        
 734        proc import out = SASVACL.NCL_Provision_Norms_Release
 735        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\NCL_Provision_Norms.xlsx"
 736        DBMS=xlsx REPLACE;
 737        sheet= 'Sheet2';
 738        Getnames=Yes;
 739        run;
 
 NOTE: The import data set has 1271 observations and 5 variables.
 NOTE: Compressing data set SASVACL.NCL_PROVISION_NORMS_RELEASE increased size by 100.00 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.20 seconds
       user cpu time       0.18 seconds
       system cpu time     0.01 seconds
       memory              914.06k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:06:04 PM
       Step Count                        62  Switch Count  47
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.NCL_PROVISION_NORMS_RELEASE data set was successfully created.
       
 
 740        
 741        /* Applying Conditions and Creating New Columns */
 742        
 743        Options Compress = Yes;
 744        Data SASVACL.PF_GCL;
 745        set SASVACL.PF_SchemeDesc;
 746        
 747        Length Recovery_Product $15;
 748        Format Recovery_Product $ 15. ;
 749        
 750        array change Total_Amt_Recovered_Ex_Penal Total_Amt_Recovered_MIO;
 751        do over change;
 752        if change = . then change = 0;
 753        end;
 754        
 755        
 756        If BM_Bucket >= 3 and NPA_STAGEID ^= 'WRITEOFF' then GCL_POOL ='Y';
 757        Else GCL_POOL ='N';
 758        
 759        If Final_Product = ''  then Final_Product = Product;
 760        Else Final_Product = Final_Product;
 761        
 762        
 763        If Product = 'X-SELL' then Recovery_Product = Sub_Product;
 764        Else if Product = 'UC-AL' then Recovery_Product = 'AL-UC' ;
 765        Else If Product = 'MORT' then Recovery_Product = Final_Product;
 766        Else Recovery_Product = Product;
 767        
 768        
 769        If GCL_POOL = 'Y' then
 770        
 771        do;
 772        
 773        If HFC_CFL ^= '' then Product_BOM = cats(HFC_CFL, BM_Bucket);
 774        Else If HFC_CFL = '' then Product_BOM = cats(Recovery_Product, BM_Bucket);
 775        
 776        If HFC_CFL ^=  '' then Product_BOM_EOM = cats(HFC_CFL, BM_Bucket, MIN_Bucket);
 777        Else If HFC_CFL = '' then Product_BOM_EOM = cats(Recovery_Product, BM_Bucket, MIN_Bucket);
 778        
 779        If HFC_CFL ^=  '' then Product_BOM_MIN = cats(HFC_CFL, BM_Bucket, MIN_Bucket);
 780        Else If HFC_CFL = '' then Product_BOM_MIN = cats(Recovery_Product, BM_Bucket, MIN_Bucket);
 781        
 782        End;
 783        
 784        Run;
 
 NOTE: There were 930407 observations read from the data set SASVACL.PF_SCHEMEDESC.
 NOTE: The data set SASVACL.PF_GCL has 930407 observations and 93 variables.
 NOTE: Compressing data set SASVACL.PF_GCL decreased size by 80.32 percent. 
       Compressed is 4467 pages; un-compressed would require 22693 pages.
 NOTE: DATA statement used (Total process time):
       real time           15.09 seconds
       user cpu time       9.79 seconds
       system cpu time     1.68 seconds
       memory              1356.93k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:06:19 PM
       Step Count                        63  Switch Count  250
       
 
 785        
 786        /* Mapping MainDatset with NCL to get GCL% and EXisting Provision% Columns */
 787        
 788        Options Compress=yes;
 789        Proc Sql;
 790        Create table SASVACL.PF_GCLRISKPERC_Final as
 791        select c.*, d.GCL_RISK_PERC, d.EXISTING_PROV_PERC
 792        from SASVACL.PF_GCL c Left Join SASVACL.NCL_Provision_Norms d
 793        on c.Product_BOM = d.Product_BOM;
 NOTE: Compressing data set SASVACL.PF_GCLRISKPERC_FINAL decreased size by 79.96 percent. 
       Compressed is 4547 pages; un-compressed would require 22693 pages.NOTE: Table SASVACL.PF_GCLRISKPERC_FINAL created, with 
                                                                               930407 rows and 95 columns.
 
 
 794        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           37.14 seconds
       user cpu time       15.46 seconds
       system cpu time     13.24 seconds
       memory              1050448.43k
       OS Memory           1075124.00k
       Timestamp           11/14/2017 12:06:56 PM
       Step Count                        64  Switch Count  98
       
 
 795        
 796        
 797        /* Mapping MainDatset with NCL to get Release% Columns */
 798        
 799        Options Compress=yes;
 800        Proc Sql;
 801        Create table SASVACL.PF_ReleasePERC as
 802        select c.*, d.PROV_Release_PERC
 803        from SASVACL.PF_GCLRISKPERC_Final c Left Join SASVACL.NCL_Provision_Norms_Release d
 804        on c.Product_BOM_EOM = d.Product_BOM_EOM;
 NOTE: Compressing data set SASVACL.PF_RELEASEPERC decreased size by 79.76 percent. 
       Compressed is 4594 pages; un-compressed would require 22693 pages.
 NOTE: Table SASVACL.PF_RELEASEPERC created, with 930407 rows and 96 columns.
 
 805        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           28.28 seconds
       user cpu time       15.39 seconds
       system cpu time     12.94 seconds
       memory              1050530.06k
       OS Memory           1075164.00k
       Timestamp           11/14/2017 12:07:24 PM
       Step Count                        65  Switch Count  109
       
 
 806        
 807        Options Compress=yes;
 808        Proc Sql;
 809        Create table SASVACL.PF_MIN_ReleasePERC as
 810        select c.*, d.PROV_Release_PERC as MIN_PROV_RELEASE_PERC
 811        from SASVACL.PF_ReleasePERC c Left Join SASVACL.NCL_Provision_Norms_Release d
 812        on c.Product_BOM_MIN = d.Product_BOM_EOM;
 NOTE: Compressing data set SASVACL.PF_MIN_RELEASEPERC decreased size by 80.05 percent. 
       Compressed is 4640 pages; un-compressed would require 23261 pages.NOTE: Table SASVACL.PF_MIN_RELEASEPERC created, with 930407 
                                                                               rows and 97 columns.
 
 
 813        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           28.34 seconds
       user cpu time       15.85 seconds
       system cpu time     13.52 seconds
       memory              1051334.12k
       OS Memory           1076028.00k
       Timestamp           11/14/2017 12:07:53 PM
       Step Count                        66  Switch Count  105
       
 
 814        
 815        
 816        
 817        /* Applying Conditions and Creating New Columns */
 818        
 819        Options Compress = Yes;
 820        Data SASVACL.PF_NewCalculations;
 821        set SASVACL.PF_MIN_ReleasePERC ;
 822        
 823        array change GCL_RISK_Perc Existing_Prov_Perc Prov_Release_Perc GCLFLOW_System POS_MIO  ;
 824        do over change;
 825        if change = . then change = 0;
 826        end;
 827        
 828        
 829        GCL_Opening_Risk = GCL_RISK_Perc * POS_MIO;
 830        
 831        Existing_Prov_Held_Amt = Existing_Prov_Perc * POS_MIO;
 832        
 833        ProvRelease_System = Prov_Release_Perc * POS_MIO;
 834        
 835        If MIN_Bucket > BM_Bucket then GCLFLOW_System = GCL_Opening_Risk;
 836        Else GCLFLOW_System = 0;
 837        
 838        NCL_Syatem = GCLFLOW_System - ProvRelease_System;
 839        
 840        If MIN_Bucket > BM_Bucket then SaveFLow_System = 'FLOW';
 841        Else SaveFLow_System = 'SAVE';
 842        
 843        
 844        
 845        Run;
 NOTE: There were 930407 observations read from the data set SASVACL.PF_MIN_RELEASEPERC.
 NOTE: The data set SASVACL.PF_NEWCALCULATIONS has 930407 observations and 103 variables.
 NOTE: Compressing data set SASVACL.PF_NEWCALCULATIONS decreased size by 80.55 percent. 
       Compressed is 4413 pages; un-compressed would require 22693 pages.
 NOTE: DATA statement used (Total process time):
       real time           13.36 seconds
       user cpu time       10.12 seconds
       system cpu time     1.56 seconds
       memory              1441.68k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:08:06 PM
       Step Count                        67  Switch Count  191
       
 
 846        
 847        /* Importing CallCenter_Agency_Master file */
 848        
 849        
 850        proc import out = SASVACL.Callcenter_Agency_Master
 851        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Callcenter_Agency_Master.xlsx"
 852        DBMS=xlsx REPLACE;
 853        sheet= 'Sheet1';
 854        Getnames=Yes;
 855        run;
 
 NOTE: The import data set has 2 observations and 3 variables.
 NOTE: Compressing data set SASVACL.CALLCENTER_AGENCY_MASTER increased size by 100.00 percent. 
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.CALLCENTER_AGENCY_MASTER data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.05 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
       memory              597.09k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:08:06 PM
       Step Count                        68  Switch Count  48
       
 
 856        
 857        /* Importing Casewise_Allocation_CM file */
 858        
 859        proc import out = SASVACL.Casewise_Allocation_CM
 860        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Casewise_Allocation_CM.xlsx"
 861        DBMS=xlsx REPLACE;
 862        sheet= 'Casewise Allocation';
 863        Getnames=Yes;
 864        run;
 
 NOTE: The import data set has 42388 observations and 5 variables.
 NOTE: Compressing data set SASVACL.CASEWISE_ALLOCATION_CM increased size by 7.69 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           2.29 seconds
       user cpu time       2.16 seconds
       Compressed is 28 pages; un-compressed would require 26 pages.
 NOTE: SASVACL.CASEWISE_ALLOCATION_CM data set was successfully created.
       system cpu time     0.07 seconds
       memory              795.84k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:08:08 PM
       Step Count                        69  Switch Count  47
       
 
 865        
 866        /* Importing CM_Mapping file */
 867        
 868        proc import out = SASVACL.CM_Mapping
 869        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\CM_Mapping.xlsx"
 870        DBMS=xlsx REPLACE;
 871        sheet= 'Sheet1';
 872        Getnames=Yes;
 873        run;
 
 NOTE: The import data set has 2064 observations and 10 variables.
 NOTE: Compressing data set SASVACL.CM_MAPPING decreased size by 44.44 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.36 seconds
       user cpu time       0.31 seconds
       system cpu time     0.01 seconds
       memory              1093.43k
       OS Memory           27224.00k
       Timestamp           11/14/2017 12:08:09 PM
       Step Count                        70  Switch Count  45
       
       Compressed is 5 pages; un-compressed would require 9 pages.
 NOTE: SASVACL.CM_MAPPING data set was successfully created.
 
 874        
 875        /* Importing Bucket_Description file */
 876        
 877        proc import out = SASVACL.BKT_DISC
 878        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\BKT_DISC.xlsx"
 879        DBMS=xlsx REPLACE;
 880        sheet= 'Sheet1';
 881        Getnames=Yes;
 882        run;
 
 NOTE: The import data set has 61 observations and 3 variables.
 NOTE: Compressing data set SASVACL.BKT_DISC increased size by 100.00 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.06 seconds
       user cpu time       0.03 seconds
       system cpu time     0.04 seconds
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.BKT_DISC data set was successfully created.
       memory              914.53k
       OS Memory           27480.00k
       Timestamp           11/14/2017 12:08:09 PM
       Step Count                        71  Switch Count  48
       
 
 883        
 884        /* Importing Product_Master file */
 885        
 886        proc import out = SASVACL.Product_Master
 887        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Product_Master.xlsx"
 888        DBMS=xlsx REPLACE;
 889        sheet= 'Sheet1';
 890        Getnames=Yes;
 891        run;
 
 NOTE: The import data set has 12 observations and 2 variables.
 NOTE: Compressing data set SASVACL.PRODUCT_MASTER increased size by 100.00 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.06 seconds
       user cpu time       0.01 seconds
       system cpu time     0.03 seconds
       memory              860.93k
       OS Memory           27480.00k
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.PRODUCT_MASTER data set was successfully created.
       Timestamp           11/14/2017 12:08:09 PM
       Step Count                        72  Switch Count  41
       
 
 892        
 893        proc import out = SASVACL.Employee_Master
 894        datafile="\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\RAW_FILES\\Employee_Master.xlsx"
 895        DBMS=xlsx REPLACE;
 896        sheet= 'Sheet1';
 897        Getnames=Yes;
 898        run;
 
 NOTE: The import data set has 239 observations and 2 variables.
 NOTE: Compressing data set SASVACL.EMPLOYEE_MASTER increased size by 100.00 percent. 
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.07 seconds
       user cpu time       0.03 seconds
       system cpu time     0.03 seconds
       memory              937.81k
       OS Memory           27480.00k
       Timestamp           11/14/2017 12:08:09 PM
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: SASVACL.EMPLOYEE_MASTER data set was successfully created.
       Step Count                        73  Switch Count  43
       
 
 899        
 900        Data SASVACL.Employee_Master;
 901        Set SASVACL.Employee_Master;
 902        EMP_CODE = (Put(Left(EMP_No), $15.));
 903        Run;
 
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       902:22   
 NOTE: There were 239 observations read from the data set SASVACL.EMPLOYEE_MASTER.
 NOTE: The data set SASVACL.EMPLOYEE_MASTER has 239 observations and 3 variables.
 NOTE: Compressing data set SASVACL.EMPLOYEE_MASTER increased size by 100.00 percent. 
       Compressed is 2 pages; un-compressed would require 1 pages.
 NOTE: DATA statement used (Total process time):
       real time           0.03 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              762.59k
       OS Memory           27480.00k
       Timestamp           11/14/2017 12:08:09 PM
       Step Count                        74  Switch Count  45
       
 
 904        
 905        /* Mapping Main Dataset with BucketDescription */
 906        
 907        
 908        Proc Sql;
 909        Create table SASVACL.PF_BKTDESC as
 910        select c.*, d.Actual_Bkt
 911        from SASVACL.PF_NewCalculations c Left Join SASVACL.BKT_DISC   d
 912        on c.Product = d.Product  and c.BOM_BKT = d.BOM_BKT;
 NOTE: Compressing data set SASVACL.PF_BKTDESC decreased size by 80.38 percent. 
       Compressed is 4452 pages; un-compressed would require 22693 pages.NOTE: Table SASVACL.PF_BKTDESC created, with 930407 rows 
                                                                               and 104 columns.
 
 
 913        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           25.31 seconds
       user cpu time       15.16 seconds
       system cpu time     13.04 seconds
       memory              1047051.09k
       OS Memory           1072372.00k
       Timestamp           11/14/2017 12:08:35 PM
       Step Count                        75  Switch Count  86
       
 
 914        
 915        Data SASVACL.PF_BKTDESC;
 916        Set SASVACL.PF_BKTDESC;
 917        Length Final_Bucket $15;
 918        Format Final_Bucket $ 15. ;
 919        Final_Bucket = Actual_Bkt;
 920        run;
 
 NOTE: There were 930407 observations read from the data set SASVACL.PF_BKTDESC.
 NOTE: The data set SASVACL.PF_BKTDESC has 930407 observations and 105 variables.
 NOTE: Compressing data set SASVACL.PF_BKTDESC decreased size by 80.21 percent. 
       Compressed is 4491 pages; un-compressed would require 22693 pages.
 NOTE: DATA statement used (Total process time):
       real time           12.17 seconds
       user cpu time       9.12 seconds
       system cpu time     1.81 seconds
       memory              1487.75k
       OS Memory           27480.00k
       Timestamp           11/14/2017 12:08:47 PM
       Step Count                        76  Switch Count  141
       
 
 921        
 922        
 923        /* Mapping Main Dataset with Product_Master */
 924        
 925        
 926        Proc Sql;
 927        Create table SASVACL.PF_ProductMaster as
 928        select c.*, d.FInal_Product_New
 929        from SASVACL.PF_BKTDESC c Left Join SASVACL.Product_Master   d
 930        on c.Product = d.Product;
 NOTE: Compressing data set SASVACL.PF_PRODUCTMASTER decreased size by 80.02 percent. 
       Compressed is 4533 pages; un-compressed would require 22693 pages.
 NOTE: Table SASVACL.PF_PRODUCTMASTER created, with 930407 rows and 106 columns.
 
 931        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           27.25 seconds
       user cpu time       15.72 seconds
       system cpu time     13.57 seconds
       memory              1052655.34k
       OS Memory           1077908.00k
       Timestamp           11/14/2017 12:09:14 PM
       Step Count                        77  Switch Count  102
       
 
 932        
 933        /* Mapping Main Dataset with CallCenter Master File */
 934        
 935        Proc Sql;
 936        Create table SASVACL.PF_CallCenter as
 937        select c.*, d.Call_Center
 938        from SASVACL.PF_ProductMaster c Left Join SASVACL.Callcenter_Agency_Master   d
 939        on c.Agency_Code = d.Agency_Code;
 NOTE: Compressing data set SASVACL.PF_CALLCENTER decreased size by 80.00 percent. 
       Compressed is 4538 pages; un-compressed would require 22693 pages.NOTE: Table SASVACL.PF_CALLCENTER created, with 930407 rows 
                                                                               and 107 columns.
 
 
 940        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           33.68 seconds
       user cpu time       15.47 seconds
       system cpu time     13.91 seconds
       memory              1052912.03k
       OS Memory           1077908.00k
       Timestamp           11/14/2017 12:09:48 PM
       Step Count                        78  Switch Count  95
       
 
 941        
 942        /* Applying Conditions and creating new columns */
 943        
 944        Data SASVACL.PF_CallCenter;
 945        Set SASVACL.PF_CallCenter;
 V#0946        
 947        If NPA_STAGEID = 'WRITEOFF' then Final_Bucket = 'Recovery';
 V$0V$1
 V$2V$3
 950        ProductCityFinal_BKT = Catt(left(Final_Product_New), left(City), left(Final_Bucket));
 951        
 952        run;
 
 NOTE: There were 930407 observations read from the data set SASVACL.PF_CALLCENTER.
 NOTE: The data set SASVACL.PF_CALLCENTER has 930407 observations and 108 variables.
 NOTE: Compressing data set SASVACL.PF_CALLCENTER decreased size by 80.70 percent. 
       Compressed is 4379 pages; un-compressed would require 22693 pages.
 NOTE: DATA statement used (Total process time):
       real time           16.49 seconds
       user cpu time       9.99 seconds
       system cpu time     1.76 seconds
       memory              1563.78k
       OS Memory           27740.00k
       Timestamp           11/14/2017 12:10:04 PM
       Step Count                        79  Switch Count  160
       
 
 953        
 954        /* Selecting required columns and renaming them also converting the type of Agreement from
 955         numeric to character */
 956        
 957        Proc Sql;
 958        Create table SASVACL.PF_CaseWiseAllocation as
 959        select Put(AgreementID, 15.) as AGID format $15., EMP_Code as EMP_Code1, Collection_Manager as Collection_Maanager1,
 960                    ACM_EMPLOYEE_CODE as ACM_EMPLOYEE_CODE1, AREA_COLLECTION_MANAGER as AREA_COLLECTION_MANAGER1
 961        from SASVACL.Casewise_Allocation_CM;
 NOTE: Compressing data set SASVACL.PF_CASEWISEALLOCATION decreased size by 9.38 percent. 
       Compressed is 29 pages; un-compressed would require 32 pages.
 NOTE: Table SASVACL.PF_CASEWISEALLOCATION created, with 42388 rows and 5 columns.
 
 962        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.09 seconds
       user cpu time       0.04 seconds
       system cpu time     0.03 seconds
       memory              3380.28k
       OS Memory           30556.00k
       Timestamp           11/14/2017 12:10:04 PM
       Step Count                        80  Switch Count  45
       
 
 963        
 964        /* Mapping Main Dataset with CaseWiseAllocation to bring EmployeeCode */
 965        
 966        Options Compress = Yes;
 967        Proc Sql;
 968        Create table SASVACL.PF_EmployeeCode as
 969        select c.*, d.EMP_Code1,
 970                     d.ACM_EMPLOYEE_CODE1
 971                    from SASVACL.PF_CallCenter c Left Join SASVACL.PF_CaseWiseAllocation d
 972        on c.AgreementID = d.AGID;
 NOTE: Compressing data set SASVACL.PF_EMPLOYEECODE decreased size by 80.58 percent. 
       Compressed is 4407 pages; un-compressed would require 22693 pages.NOTE: Table SASVACL.PF_EMPLOYEECODE created, with 930407 
                                                                               rows and 110 columns.
 
 
 973        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           37.21 seconds
       user cpu time       17.75 seconds
       system cpu time     16.31 seconds
       memory              1053129.84k
       OS Memory           1078428.00k
       Timestamp           11/14/2017 12:10:42 PM
       Step Count                        81  Switch Count  118
       
 
 974        
 975        /* Mapping Main Dataset with CM_Mapping to bring CM_Emp_Code */
 976        
 977        Proc Sql;
 978        Create table SASVACL.PF_CM_Mapping as
 979        select c.*, d.CM_EMP_Code,
 980                     d.ACM_EMP_CODE
 981                    from SASVACL.PF_EmployeeCode c Left Join SASVACL.CM_Mapping d
 982        on c.ProductCityFinal_BKT = d.ProductCityFinal_BKT ;
 NOTE: Compressing data set SASVACL.PF_CM_MAPPING decreased size by 80.51 percent. 
       Compressed is 4534 pages; un-compressed would require 23261 pages.NOTE: Table SASVACL.PF_CM_MAPPING created, with 930407 rows 
                                                                               and 112 columns.
 
 
 983        quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           37.53 seconds
       user cpu time       18.97 seconds
       system cpu time     15.74 seconds
       memory              1053414.78k
       OS Memory           1079256.00k
       Timestamp           11/14/2017 12:11:19 PM
       Step Count                        82  Switch Count  110
       
 
 984        
 985        
 986        Options Compress = Yes;
 987        Data SASVACL.PF_EmpCode;
 988        Set SASVACL.PF_CM_Mapping;
 989        
 990        Length Final_Emp_Code $20;
 991        
 992        If Emp_Code1 NE '' then Final_Emp_Code = Emp_Code1;
 993        eLse If CM_EMP_Code NE '' and Call_Center NE 'Y' then Final_Emp_Code = CM_EMP_Code;
 994        
 995        
 996        If ACM_EMPLOYEE_CODE1 NE '' then Final_ACM_EMP_CODE = ACM_EMPLOYEE_CODE1;
 997        eLse If ACM_EMP_CODE NE '' and Call_Center NE 'Y' then Final_ACM_EMP_CODE = ACM_EMP_CODE;
 998        
 999        If Total_Amt_Recovered_Ex_Penal_MIO >= POS_MIO then Recovery_NCL = POS_MIO;
 1000       Else Recovery_NCL = Total_Amt_Recovered_Ex_Penal_MIO;
 1001       
 1002       run;
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       992:17   
 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
       992:42   
 NOTE: Variable Total_Amt_Recovered_Ex_Penal_MIO is uninitialized.
 NOTE: There were 930407 observations read from the data set SASVACL.PF_CM_MAPPING.
 NOTE: The data set SASVACL.PF_EMPCODE has 930407 observations and 116 variables.
 NOTE: Compressing data set SASVACL.PF_EMPCODE decreased size by 80.11 percent. 
       Compressed is 4514 pages; un-compressed would require 22693 pages.
 NOTE: DATA statement used (Total process time):
       real time           13.64 seconds
       user cpu time       10.23 seconds
       system cpu time     1.56 seconds
       memory              1767.75k
       OS Memory           28256.00k
       Timestamp           11/14/2017 12:11:33 PM
       Step Count                        83  Switch Count  194
       
 
 1003       
 1004       
 1005       Options Compress = Yes;
 1006       Proc Sql;
 1007       Create table SASVACL.PF_MappingCMName as
 1008       select c.*, d.Name as CM_Name
 1009       from SASVACL.PF_EmpCode c Left JOIN SASVACL.Employee_Master d
 1010       on c.FINAL_EMP_CODE = d.EMP_CODE;
 NOTE: Compressing data set SASVACL.PF_MAPPINGCMNAME decreased size by 80.27 percent. 
       Compressed is 4589 pages; un-compressed would require 23261 pages.
 NOTE: Table SASVACL.PF_MAPPINGCMNAME created, with 930407 rows and 117 columns.
 
 1011       quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           29.20 seconds
       user cpu time       16.03 seconds
       system cpu time     15.64 seconds
       memory              1053339.57k
       OS Memory           1079196.00k
       Timestamp           11/14/2017 12:12:02 PM
       Step Count                        84  Switch Count  96
       
 
 1012       
 1013       Options Compress = Yes;
 1014       Proc Sql;
 1015       Create table SASVACL.PF_ACMName as
 1016       select c.*, d.Name as ACM_Name
 1017       from SASVACL.PF_MappingCMName c Left JOIN SASVACL.Employee_Master d
 1018       on c.Final_ACM_EMP_CODE = d.EMP_CODE;
 NOTE: Compressing data set SASVACL.PF_ACMNAME decreased size by 80.27 percent. 
       Compressed is 4590 pages; un-compressed would require 23261 pages.NOTE: Table SASVACL.PF_ACMNAME created, with 930407 rows 
                                                                               and 118 columns.
 
 
 1019       quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           37.14 seconds
       user cpu time       16.91 seconds
       system cpu time     16.77 seconds
       memory              1053297.15k
       OS Memory           1078940.00k
       Timestamp           11/14/2017 12:12:39 PM
       Step Count                        85  Switch Count  111
       
 
 1020       
 1021       
 1022       Options Compress = Yes;
 1023       Data SASVACL.PF_FINAL;
 1024       Set SASVACL.PF_ACMName;
 1025       
 V#01026       If Final_Summary = 'ROLL BACK' then Roll_BACK_POS = POS_MIO;
 1027       Else Roll_BACK_POS = 0;
 1028       
 1029       If Final_Summary = 'NORMALISED' then NORMALISED_POS = POS_MIO;
 1030       Else NORMALISED_POS = 0;
 1031       
 1032       If Final_Summary = 'OUT OFF 30+' then OUT_OFF_30_POS = POS_MIO;
 1033       Else OUT_OFF_30_POS = 0;
 1034       
 1035       If Final_Summary = 'STAB' then STAB_POS = POS_MIO;
 1036       Else STAB_POS = 0;
 1037       
 1038       If Final_Summary = 'ROLL FORWARD' then Roll_Forward_POS = POS_MIO;
 1039       Else Roll_Forward_POS = 0;
 1040       
 1041       
 1042       If Final_Product_New = 'X-SELL' and Bm_Bucket IN (4, 5) and MIN_Bucket <= 3 Then Write_Back = POS_MIO;
 1043       Else Write_Back = 0;
 1044       
 1045       If Final_Summary = 'WRTIE BACK' then WRITE_BACK_POS = POS_MIO;
 1046       Else WRITE_BACK_POS = 0;
 1047       
 1048       If Final_Summary = 'ROLL FORWARD' then Cured_POS = 0;
 1049       Else IF Final_Summary = 'C-OFF' then Cured_POS = Total_Amt_Recovered_MIO;
 1050       Else Cured_POS = POS_MIO;
 1051       
 1052       If Final_Summary = 'ROLL FORWARD' then Cured = 0;
 1053       Else If Final_Summary = 'C-OFF' and Total_Amt_Recovered_MIO > 0  then Cured = 1;
 1054       Else Cured = 1;
 1055       
 1056       If Final_Summary = 'ROLL BACK' then Count_Roll_BACK = Cured;
 1057       Else Count_Roll_BACK = 0;
 1058       
 1059       If Final_Summary = 'NORMALISED' then Count_NORMALISED = Cured;
 1060       Else Count_NORMALISED = 0;
 1061       
 1062       If Final_Summary = 'OUT OFF 30+' then Count_OUT_OFF_30 = Cured;
 1063       Else Count_OUT_OFF_30 = 0;
 1064       
 1065       If Final_Summary = 'STAB' then Count_STAB = Cured;
 1066       Else Count_STAB = 0;
 1067       
 1068       If Final_Summary = 'ROLL FORWARD' then Count_Roll_Forward = 1;
 1069       Else Count_Roll_Forward = 0;
 1070       
 1071       If Final_Summary = 'WRTIE BACK' then Count_WRITE_BACK = Cured;
 1072       Else Count_WRITE_BACK = 0;
 1073       
 1074       If Final_Summary = 'C-OFF' and Total_Amt_Recovered_MIO > 0  then Count_C_OFF = Cured;
 1075       Else Count_C_OFF = 0;
 1076       Run;
 NOTE: There were 930407 observations read from the data set SASVACL.PF_ACMNAME.
 NOTE: The data set SASVACL.PF_FINAL has 930407 observations and 134 variables.
 NOTE: Compressing data set SASVACL.PF_FINAL decreased size by 80.58 percent. 
       Compressed is 4407 pages; un-compressed would require 22693 pages.
 NOTE: DATA statement used (Total process time):
       real time           14.51 seconds
       user cpu time       11.79 seconds
       system cpu time     1.79 seconds
       memory              1681.34k
       OS Memory           28512.00k
       Timestamp           11/14/2017 12:12:54 PM
       Step Count                        86  Switch Count  274
       
 
 1077       
 1078       Options Compress = yes;
 1079       DATA SASVACL.Collections;
 1080       SET  SASVACL.PF_FINAL;
 1081       KEEP  AGREEMENTID  AGREEMENTNO  PRODUCTFLAG ALLOCATION  ALLOCATION_DATE  BOM_BKT
 1082       SCHME_DESC  PRODUCT  BIL_Prod_Type  CITY  STATE  REGION  CATEGORY  Priority  segment
 1083       CUSTOMERNAME  POS_MIO  BM_Bucket  Dac_Summary  Total_Collection  Peak_Bucket  As_ON_Date_Bucket
 1084       Current_Bkt  Min_Bucket  Final_Summary  Recovery  Agency_Code  Agency_Name  Count
 1085       Total_Amt_Recovered_Ex_Penal  Total_Amt_Recovered_MIO  Final_Product  Recovery_Product
 1086       GCL_RISK_Perc  GCLFLOW_System  GCL_Opening_Risk  ProvRelease_System  NCL_Syatem
 1087       SaveFLow_System  Recovery_NCL  Final_Bucket  Final_product_NEW  Call_Center  Final_Emp_Code
 1088       Final_ACM_EMP_CODE  CM_Name  ACM_Name  Cured_POS ;
 1089       Run;
 
 NOTE: DATA statement used (Total process time):
       real time           8.53 seconds
       user cpu time       6.24 seconds
       system cpu time     1.48 seconds
       memory              1454.53k
       OS Memory           28252.00k
       Timestamp           11/14/2017 12:13:02 PM
       Step Count                        87  Switch Count  139
       
 NOTE: There were 930407 observations read from the data set SASVACL.PF_FINAL.
 NOTE: The data set SASVACL.COLLECTIONS has 930407 observations and 48 variables.
 NOTE: Compressing data set SASVACL.COLLECTIONS decreased size by 71.76 percent. 
       Compressed is 3864 pages; un-compressed would require 13683 pages.
 
 1090       
 1091       
 1092       Proc Export
 1093       Data = SASVACL.Collections
 1094       Outfile = "\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\Collections_&SYSDATE..CSV"
 SYMBOLGEN:  Macro variable SYSDATE resolves to 14NOV17
 1095       Replace
 1096       DBMS = DLM ;
 1097       DELIMITER = ',';
 1098       Run;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 1099        /**********************************************************************
 1100        *   PRODUCT:   SAS
 1101        *   VERSION:   9.4
 1102        *   CREATOR:   External File Interface
 1103        *   DATE:      14NOV17
 1104        *   DESC:      Generated SAS Datastep Code
 1105        *   TEMPLATE SOURCE:  (None Specified.)
 1106        ***********************************************************************/
 1107           data _null_;
 1108           %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 1109           %let _EFIREC_ = 0;     /* clear export record count macro variable */
 1110           file '\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\Collections_14NOV17.CSV' delimiter=',' DSD DROPOVER
 1110     ! lrecl=32767;
 1111           if _n_ = 1 then        /* write column names or labels */
 1112            do;
 1113              put
 1114                 "ALLOCATION"
 1115              ','
 1116                 "ALLOCATION_DATE"
 1117              ','
 1118                 "BOM_BKT"
 1119              ','
 1120                 "AGREEMENTID"
 1121              ','
 1122                 "AGREEMENTNO"
 1123              ','
 1124                 "PRODUCTFLAG"
 1125              ','
 1126                 "SCHME_DESC"
 1127              ','
 1128                 "PRODUCT"
 1129              ','
 1130                 "BIL_Prod_Type"
 1131              ','
 1132                 "CITY"
 1133              ','
 1134                 "STATE"
 1135              ','
 1136                 "REGION"
 1137              ','
 1138                 "CATEGORY"
 1139              ','
 1140                 "Priority"
 1141              ','
 1142                 "segment"
 1143              ','
 1144                 "CUSTOMERNAME"
 1145              ','
 1146                 "POS_MIO"
 1147              ','
 1148                 "BM_Bucket"
 1149              ','
 1150                 "Current_Bkt"
 1151              ','
 1152                 "Dac_Summary"
 1153              ','
 1154                 "Total_Collection"
 1155              ','
 1156                 "Peak_Bucket"
 1157              ','
 1158                 "As_ON_Date_Bucket"
 1159              ','
 1160                 "Min_Bucket"
 1161              ','
 1162                 "Final_Summary"
 1163              ','
 1164                 "Recovery"
 1165              ','
 1166                 "Agency_Code"
 1167              ','
 1168                 "Agency_Name"
 1169              ','
 1170                 "Count"
 1171              ','
 1172                 "Total_Amt_Recovered_Ex_Penal"
 1173              ','
 1174                 "Total_Amt_Recovered_MIO"
 1175              ','
 1176                 "Final_Product"
 1177              ','
 1178                 "Recovery_Product"
 1179              ','
 1180                 "GCL_RISK_Perc"
 1181              ','
 1182                 "GCLFLOW_System"
 1183              ','
 1184                 "GCL_Opening_Risk"
 1185              ','
 1186                 "ProvRelease_System"
 1187              ','
 1188                 "NCL_Syatem"
 1189              ','
 1190                 "SaveFLow_System"
 1191              ','
 1192                 "Final_Bucket"
 1193              ','
 1194                 "Final_product_NEW"
 1195              ','
 1196                 "Call_Center"
 1197              ','
 1198                 "Final_Emp_Code"
 1199              ','
 1200                 "Final_ACM_EMP_CODE"
 1201              ','
 1202                 "Recovery_NCL"
 1203              ','
 1204                 "CM_Name"
 1205              ','
 1206                 "ACM_Name"
 1207              ','
 1208                 "Cured_POS"
 1209              ;
 1210            end;
 1211          set  SASVACL.COLLECTIONS   end=EFIEOD;
 1212              format ALLOCATION $50. ;
 1213              format ALLOCATION_DATE date9. ;
 1214              format BOM_BKT $30. ;
 1215              format AGREEMENTID $char22. ;
 1216              format AGREEMENTNO $char8. ;
 1217              format PRODUCTFLAG $char6. ;
 1218              format SCHME_DESC $char50. ;
 1219              format PRODUCT $20. ;
 1220              format BIL_Prod_Type $char7. ;
 1221              format CITY $char16. ;
 1222              format STATE $char14. ;
 1223              format REGION $char5. ;
 1224              format CATEGORY $10. ;
 1225              format Priority best1. ;
 1226              format segment $30. ;
 1227              format CUSTOMERNAME $300. ;
 1228              format POS_MIO best12. ;
 1229              format BM_Bucket best12. ;
 1230              format Current_Bkt best12. ;
 1231              format Dac_Summary $15. ;
 1232              format Total_Collection best12. ;
 1233              format Peak_Bucket best12. ;
 1234              format As_ON_Date_Bucket best12. ;
 1235              format Min_Bucket best12. ;
 1236              format Final_Summary $12. ;
 1237              format Recovery best12. ;
 1238              format Agency_Code $20. ;
 1239              format Agency_Name $30. ;
 1240              format Count best12. ;
 1241              format Total_Amt_Recovered_Ex_Penal best12. ;
 1242              format Total_Amt_Recovered_MIO best12. ;
 1243              format Final_Product $7. ;
 1244              format Recovery_Product $15. ;
 1245              format GCL_RISK_Perc comma15.2 ;
 1246              format GCLFLOW_System best12. ;
 1247              format GCL_Opening_Risk best12. ;
 1248              format ProvRelease_System best12. ;
 1249              format NCL_Syatem best12. ;
 1250              format SaveFLow_System $4. ;
 1251              format Final_Bucket $15. ;
 1252              format Final_product_NEW $7. ;
 1253              format Call_Center $1. ;
 1254              format Final_Emp_Code $20. ;
 1255              format Final_ACM_EMP_CODE $1. ;
 1256              format Recovery_NCL best12. ;
 1257              format CM_Name $35. ;
 1258              format ACM_Name $35. ;
 1259              format Cured_POS best12. ;
 1260            do;
 1261              EFIOUT + 1;
 1262              put ALLOCATION $ @;
 1263              put ALLOCATION_DATE @;
 1264              put BOM_BKT $ @;
 1265              put AGREEMENTID $ @;
 1266              put AGREEMENTNO $ @;
 1267              put PRODUCTFLAG $ @;
 1268              put SCHME_DESC $ @;
 1269              put PRODUCT $ @;
 1270              put BIL_Prod_Type $ @;
 1271              put CITY $ @;
 1272              put STATE $ @;
 1273              put REGION $ @;
 1274              put CATEGORY $ @;
 1275              put Priority @;
 1276              put segment $ @;
 1277              put CUSTOMERNAME $ @;
 1278              put POS_MIO @;
 1279              put BM_Bucket @;
 1280              put Current_Bkt @;
 1281              put Dac_Summary $ @;
 1282              put Total_Collection @;
 1283              put Peak_Bucket @;
 1284              put As_ON_Date_Bucket @;
 1285              put Min_Bucket @;
 1286              put Final_Summary $ @;
 1287              put Recovery @;
 1288              put Agency_Code $ @;
 1289              put Agency_Name $ @;
 1290              put Count @;
 1291              put Total_Amt_Recovered_Ex_Penal @;
 1292              put Total_Amt_Recovered_MIO @;
 1293              put Final_Product $ @;
 1294              put Recovery_Product $ @;
 1295              put GCL_RISK_Perc @;
 1296              put GCLFLOW_System @;
 1297              put GCL_Opening_Risk @;
 1298              put ProvRelease_System @;
 1299              put NCL_Syatem @;
 1300              put SaveFLow_System $ @;
 1301              put Final_Bucket $ @;
 1302              put Final_product_NEW $ @;
 1303              put Call_Center $ @;
 1304              put Final_Emp_Code $ @;
 1305              put Final_ACM_EMP_CODE $ @;
 1306              put Recovery_NCL @;
 1307              put CM_Name $ @;
 1308              put ACM_Name $ @;
 1309              put Cured_POS ;
 1310              ;
 1311            end;
 1312           if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 1313           if EFIEOD then call symputx('_EFIREC_',EFIOUT);
 1314           run;
 
 NOTE: The file '\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\Collections_14NOV17.CSV' is:
       Filename=\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\Collections_14NOV17.CSV,
       RECFM=V,LRECL=32767,File Size (bytes)=0,
       Last Modified=14Nov2017:12:13:03,
       Create Time=14Nov2017:12:13:03
 
 NOTE: 930408 records were written to the file '\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\Collections_14NOV17.CSV'.
       The minimum record length was 197.
       The maximum record length was 606.
 NOTE: There were 930407 observations read from the data set SASVACL.COLLECTIONS.
 NOTE: DATA statement used (Total process time):
       real time           13.40 seconds
       user cpu time       12.10 seconds
       system cpu time     0.98 seconds
       memory              9632.89k
       OS Memory           33884.00k
       Timestamp           11/14/2017 12:13:16 PM
       Step Count                        88  Switch Count  472
       
 
 930407 records created in \\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\Collections_14NOV17.CSV from SASVACL.COLLECTIONS.
   
   
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           13.55 seconds
 NOTE: "\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\Collections_14NOV17.CSV" file was successfully created.
       user cpu time       12.16 seconds
       system cpu time     1.06 seconds
       memory              9632.89k
       OS Memory           34140.00k
       Timestamp           11/14/2017 12:13:16 PM
       Step Count                        88  Switch Count  58
       
 
 1315       
 1316       Proc Export
 1317       Data = SASVACL.PF_FINAL
 1318       Outfile = "\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\PF_FINAL_&SYSDATE..CSV"
 SYMBOLGEN:  Macro variable SYSDATE resolves to 14NOV17
 1319       Replace
 1320       DBMS = DLM ;
 1321       DELIMITER = ',';
 1322       Run;
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 1323        /**********************************************************************
 1324        *   PRODUCT:   SAS
 1325        *   VERSION:   9.4
 1326        *   CREATOR:   External File Interface
 1327        *   DATE:      14NOV17
 1328        *   DESC:      Generated SAS Datastep Code
 1329        *   TEMPLATE SOURCE:  (None Specified.)
 1330        ***********************************************************************/
 1331           data _null_;
 1332           %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 1333           %let _EFIREC_ = 0;     /* clear export record count macro variable */
 1334           file '\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\PF_FINAL_14NOV17.CSV' delimiter=',' DSD DROPOVER lrecl=32767;
 1335           if _n_ = 1 then        /* write column names or labels */
 1336            do;
 1337              put
 1338                 "ALLOCATION"
 1339              ','
 1340                 "ALLOCATION_DATE"
 1341              ','
 1342                 "BOM_BKT"
 1343              ','
 1344                 "AGREEMENTID"
 1345              ','
 1346                 "AGREEMENTNO"
 1347              ','
 1348                 "BRANCH"
 1349              ','
 1350                 "PRODUCTFLAG"
 1351              ','
 1352                 "SCHME_DESC"
 1353              ','
 1354                 "SANCT_DATE"
 1355              ','
 1356                 "DISB_AMT"
 1357              ','
 1358                 "DISB_DATE"
 1359              ','
 1360                 "EMI_START_DATE"
 1361              ','
 1362                 "NPA_STAGEID"
 1363              ','
 1364                 "NPA_DATE"
 1365              ','
 1366                 "EMI_AMT"
 1367              ','
 1368                 "EMI_OD_AMT"
 1369              ','
 1370                 "NO_OF_EMI_OD"
 1371              ','
 1372                 "BUCKET"
 1373              ','
 1374                 "DPD"
 1375              ','
 1376                 "PRINCIPAL_OUTSTANDING"
 1377              ','
 1378                 "INTEREST_OD"
 1379              ','
 1380                 "NONSTARTER"
 1381              ','
 1382                 "PRODUCT"
 1383              ','
 1384                 "BIL_Prod_Type"
 1385              ','
 1386                 "CITY"
 1387              ','
 1388                 "STATE"
 1389              ','
 1390                 "REGION"
 1391              ','
 1392                 "CATEGORY"
 1393              ','
 1394                 "Priority"
 1395              ','
 1396                 "segment"
 1397              ','
 1398                 "CRN"
 1399              ','
 1400                 "CUSTOMERNAME"
 1401              ','
 1402                 "REFERRER_NAME"
 1403              ','
 1404                 "PENAL_PENDING"
 1405              ','
 1406                 "BCC_PENDING"
 1407              ','
 1408                 "POS_MIO"
 1409              ','
 1410                 "BM_Bucket"
 1411              ','
 1412                 "Current_Date"
 1413              ','
 1414                 "ONOFF_BOOK"
 1415              ','
 1416                 "Current_Bkt"
 1417              ','
 1418                 "System_Summary"
 1419              ','
 1420                 "MOB"
 1421              ','
 1422                 "Sub_Product"
 1423              ','
 1424                 "DAC"
 1425              ','
 1426                 "Penal_Bounce"
 1427              ','
 1428                 "Reference_NO"
 1429              ','
 1430                 "Paytm"
 1431              ','
 1432                 "QP"
 1433              ','
 1434                 "Axis"
 1435              ','
 1436                 "Receipt_AMT"
 1437              ','
 1438                 "Presentation"
 1439              ','
 1440                 "Status_Tpsl"
 1441              ','
 1442                 "SpecialPresentation"
 1443              ','
 1444                 "Discrepancy"
 1445              ','
 1447              ','
 1448                 "Dac_Summary"
 1449              ','
 1450                 "Total_Collection"
 1451              ','
 1452                 "EMI_Collected"
 1453              ','
 1454                 "Peak_Bucket"
 1455              ','
 1456                 "As_ON_Date_Bucket"
 1457              ','
 1458                 "Min_Bucket"
 1459              ','
 1460                 "Final_Summary"
 1461              ','
 1462                 "Recovery"
 1463              ','
 1464                 "PhysicalCollection_POS"
 1465              ','
 1466                 "SystemCollection_POS"
 1467              ','
 1468                 "Agency_Code"
 1469              ','
 1470                 "Agency_Name"
 1471              ','
 1472                 "Trail_Status"
 1473              ','
 1474                 "Roll_Forward"
 1475              ','
 1476                 "GAP"
 1477              ','
 1478                 "Count"
 1479              ','
 1480                 "Concatenate_Map"
 1481              ','
 1482                 "POS_Recovery_Cash"
 1483              ','
 1484                 "POS_Recovery_DAC"
 1485              ','
 1486                 "Bounce_Late_Recovery"
 1487              ','
 1488                 "Presentation_Clearance"
 1489              ','
 1490                 "Additional_Payment"
 1491              ','
 1492                 "DAC_Recovery_Tag"
 1493              ','
 1494                 "Cash_Recovery_Tag"
 1495              ','
 1496                 "Presentation_Recovery_Tag"
 1497              ','
 1498                 "Bounce_Recovery_Tag"
 1499              ','
 1500                 "Concat_Recovery"
 1501              ','
 1502                 "Total_Amt_Recovered_Ex_Penal"
 1503              ','
 1504                 "Total_Amt_Recovered_MIO"
 1505              ','
 1506                 "Recovery_Percentage"
 1507              ','
 1508                 "Recovery_Mode"
 1509              ','
 1510                 "Final_Product"
 1511              ','
 1512                 "HFC_CFL"
 1513              ','
 1514                 "Recovery_Product"
 1515              ','
 1516                 "GCL_POOL"
 1517              ','
 1518                 "Product_BOM"
 1519              ','
 1520                 "Product_BOM_EOM"
 1521              ','
 1522                 "Product_BOM_MIN"
 1523              ','
 1524                 "GCL_RISK_Perc"
 1525              ','
 1526                 "EXISTING_PROV_Perc"
 1527              ','
 1528                 "PROV_RELEASE_Perc"
 1529              ','
 1530                 "MIN_PROV_RELEASE_PERC"
 1531              ','
 1532                 "GCLFLOW_System"
 1533              ','
 1534                 "GCL_Opening_Risk"
 1535              ','
 1536                 "Existing_Prov_Held_Amt"
 1537              ','
 1538                 "ProvRelease_System"
 1539              ','
 1540                 "NCL_Syatem"
 1541              ','
 1542                 "SaveFLow_System"
 1543              ','
 1544                 "Actual_BKT"
 1545              ','
 1546                 "Final_Bucket"
 1547              ','
 1548                 "Final_product_NEW"
 1549              ','
 1550                 "Call_Center"
 1551              ','
 1552                 "ProductCityFinal_BKT"
 1553              ','
 1554                 "EMP_Code1"
 1555              ','
 1556                 "ACM_EMPLOYEE_CODE1"
 1557              ','
 1558                 "CM_EMP_Code"
 1559              ','
 1560                 "ACM_EMP_Code"
 1561              ','
 1562                 "Final_Emp_Code"
 1563              ','
 1564                 "Final_ACM_EMP_CODE"
 1565              ','
 1566                 "Total_Amt_Recovered_Ex_Penal_MIO"
 1567              ','
 1568                 "Recovery_NCL"
 1569              ','
 1570                 "CM_Name"
 1571              ','
 1572                 "ACM_Name"
 1573              ','
 1574                 "Roll_BACK_POS"
 1575              ','
 1576                 "NORMALISED_POS"
 1577              ','
 1578                 "OUT_OFF_30_POS"
 1579              ','
 1580                 "STAB_POS"
 1581              ','
 1582                 "Roll_Forward_POS"
 1583              ','
 1584                 "Write_Back"
 1585              ','
 1586                 "WRITE_BACK_POS"
 1587              ','
 1588                 "Cured_POS"
 1589              ','
 1590                 "Cured"
 1591              ','
 1592                 "Count_Roll_BACK"
 1593              ','
 1594                 "Count_NORMALISED"
 1595              ','
 1596                 "Count_OUT_OFF_30"
 1597              ','
 1598                 "Count_STAB"
 1599              ','
 1600                 "Count_Roll_Forward"
 1601              ','
 1602                 "Count_WRITE_BACK"
 1603              ','
 1604                 "Count_C_OFF"
 1605              ;
 1606            end;
 1607          set  SASVACL.PF_FINAL   end=EFIEOD;
 1608              format ALLOCATION $50. ;
 1609              format ALLOCATION_DATE date9. ;
 1610              format BOM_BKT $30. ;
 1611              format AGREEMENTID $char22. ;
 1612              format AGREEMENTNO $char8. ;
 1613              format BRANCH $char16. ;
 1614              format PRODUCTFLAG $char6. ;
 1615              format SCHME_DESC $char50. ;
 1616              format SANCT_DATE date9. ;
 1617              format DISB_AMT best11. ;
 1618              format DISB_DATE date9. ;
 1619              format EMI_START_DATE date9. ;
 1620              format NPA_STAGEID $char8. ;
 1621              format NPA_DATE date9. ;
 1622              format EMI_AMT best9. ;
 1623              format EMI_OD_AMT best10. ;
 1624              format NO_OF_EMI_OD best2. ;
 1625              format BUCKET $char3. ;
 1626              format DPD best4. ;
 1627              format PRINCIPAL_OUTSTANDING best12. ;
 1628              format INTEREST_OD best11. ;
 1629              format NONSTARTER $char11. ;
 1630              format PRODUCT $20. ;
 1631              format BIL_Prod_Type $char7. ;
 1632              format CITY $char16. ;
 1633              format STATE $char14. ;
 1634              format REGION $char5. ;
 1635              format CATEGORY $10. ;
 1636              format Priority best1. ;
 1637              format segment $30. ;
 1639              format CUSTOMERNAME $300. ;
 1640              format REFERRER_NAME $char30. ;
 1641              format PENAL_PENDING best12. ;
 1642              format BCC_PENDING best8. ;
 1643              format POS_MIO best12. ;
 1644              format BM_Bucket best12. ;
 1645              format Current_Date date9. ;
 1646              format ONOFF_BOOK $8. ;
 1647              format Current_Bkt best12. ;
 1648              format System_Summary $15. ;
 1649              format MOB best12. ;
 1650              format Sub_Product $5. ;
 1651              format DAC best12. ;
 1652              format Penal_Bounce best12. ;
 1653              format Reference_NO $15. ;
 1654              format Paytm best12. ;
 1655              format QP best12. ;
 1656              format Axis best12. ;
 1657              format Receipt_AMT best12. ;
 1658              format Presentation 10. ;
 1659              format Status_Tpsl $10. ;
 1660              format SpecialPresentation best12. ;
 1661              format Discrepancy best12. ;
 1662              format AdditionalPayment best12. ;
 1663              format Dac_Summary $15. ;
 1664              format Total_Collection best12. ;
 1665              format EMI_Collected best12. ;
 1666              format Peak_Bucket best12. ;
 1667              format As_ON_Date_Bucket best12. ;
 1668              format Min_Bucket best12. ;
 1669              format Final_Summary $12. ;
 1670              format Recovery best12. ;
 1671              format PhysicalCollection_POS best12. ;
 1672              format SystemCollection_POS best12. ;
 1673              format Agency_Code $20. ;
 1674              format Agency_Name $30. ;
 1675              format Trail_Status $14. ;
 1676              format Roll_Forward best12. ;
 1677              format GAP best12. ;
 1678              format Count best12. ;
 1679              format Concatenate_Map $200. ;
 1680              format POS_Recovery_Cash best12. ;
 1681              format POS_Recovery_DAC best12. ;
 1682              format Bounce_Late_Recovery best12. ;
 1683              format Presentation_Clearance best12. ;
 1684              format Additional_Payment best12. ;
 1685              format DAC_Recovery_Tag best12. ;
 1686              format Cash_Recovery_Tag best12. ;
 1687              format Presentation_Recovery_Tag best12. ;
 1688              format Bounce_Recovery_Tag best12. ;
 1689              format Concat_Recovery $200. ;
 1690              format Total_Amt_Recovered_Ex_Penal best12. ;
 1691              format Total_Amt_Recovered_MIO best12. ;
 1692              format Recovery_Percentage best12. ;
 1693              format Recovery_Mode $32. ;
 1694              format Final_Product $7. ;
 1695              format HFC_CFL $3. ;
 1696              format Recovery_Product $15. ;
 1697              format GCL_POOL $1. ;
 1698              format Product_BOM $200. ;
 1699              format Product_BOM_EOM $200. ;
 1700              format Product_BOM_MIN $200. ;
 1701              format GCL_RISK_Perc comma15.2 ;
 1702              format EXISTING_PROV_Perc comma15.2 ;
 1703              format PROV_RELEASE_Perc comma15.2 ;
 1704              format MIN_PROV_RELEASE_PERC comma15.2 ;
 1705              format GCLFLOW_System best12. ;
 1706              format GCL_Opening_Risk best12. ;
 1707              format Existing_Prov_Held_Amt best12. ;
 1708              format ProvRelease_System best12. ;
 1709              format NCL_Syatem best12. ;
 1710              format SaveFLow_System $4. ;
 1711              format Actual_BKT $6. ;
 1712              format Final_Bucket $15. ;
 1713              format Final_product_NEW $7. ;
 1714              format Call_Center $1. ;
 1715              format ProductCityFinal_BKT $200. ;
 1716              format EMP_Code1 best12. ;
 1717              format ACM_EMPLOYEE_CODE1 $1. ;
 1718              format CM_EMP_Code $41. ;
 1719              format ACM_EMP_Code $5. ;
 1720              format Final_Emp_Code $20. ;
 1721              format Final_ACM_EMP_CODE $1. ;
 1722              format Total_Amt_Recovered_Ex_Penal_MIO best12. ;
 1723              format Recovery_NCL best12. ;
 1724              format CM_Name $35. ;
 1725              format ACM_Name $35. ;
 1726              format Roll_BACK_POS best12. ;
 1727              format NORMALISED_POS best12. ;
 1728              format OUT_OFF_30_POS best12. ;
 1729              format STAB_POS best12. ;
 1730              format Roll_Forward_POS best12. ;
 1731              format Write_Back best12. ;
 1732              format WRITE_BACK_POS best12. ;
 1733              format Cured_POS best12. ;
 1734              format Cured best12. ;
 1735              format Count_Roll_BACK best12. ;
 1736              format Count_NORMALISED best12. ;
 1737              format Count_OUT_OFF_30 best12. ;
 1738              format Count_STAB best12. ;
 1739              format Count_Roll_Forward best12. ;
 1740              format Count_WRITE_BACK best12. ;
 1741              format Count_C_OFF best12. ;
 1742            do;
 1743              EFIOUT + 1;
 1744              put ALLOCATION $ @;
 1745              put ALLOCATION_DATE @;
 1746              put BOM_BKT $ @;
 1747              put AGREEMENTID $ @;
 1748              put AGREEMENTNO $ @;
 1749              put BRANCH $ @;
 1750              put PRODUCTFLAG $ @;
 1751              put SCHME_DESC $ @;
 1752              put SANCT_DATE @;
 1753              put DISB_AMT @;
 1754              put DISB_DATE @;
 1755              put EMI_START_DATE @;
 1756              put NPA_STAGEID $ @;
 1757              put NPA_DATE @;
 1758              put EMI_AMT @;
 1759              put EMI_OD_AMT @;
 1760              put NO_OF_EMI_OD @;
 1761              put BUCKET $ @;
 1763              put PRINCIPAL_OUTSTANDING @;
 1764              put INTEREST_OD @;
 1765              put NONSTARTER $ @;
 1766              put PRODUCT $ @;
 1767              put BIL_Prod_Type $ @;
 1768              put CITY $ @;
 1769              put STATE $ @;
 1770              put REGION $ @;
 1771              put CATEGORY $ @;
 1772              put Priority @;
 1773              put segment $ @;
 1774              put CRN @;
 1775              put CUSTOMERNAME $ @;
 1776              put REFERRER_NAME $ @;
 1777              put PENAL_PENDING @;
 1778              put BCC_PENDING @;
 1779              put POS_MIO @;
 1780              put BM_Bucket @;
 1781              put Current_Date @;
 1782              put ONOFF_BOOK $ @;
 1783              put Current_Bkt @;
 1784              put System_Summary $ @;
 1785              put MOB @;
 1786              put Sub_Product $ @;
 1787              put DAC @;
 1788              put Penal_Bounce @;
 1789              put Reference_NO $ @;
 1790              put Paytm @;
 1791              put QP @;
 1792              put Axis @;
 1793              put Receipt_AMT @;
 1794              put Presentation @;
 1795              put Status_Tpsl $ @;
 1796              put SpecialPresentation @;
 1797              put Discrepancy @;
 1798              put AdditionalPayment @;
 1799              put Dac_Summary $ @;
 1800              put Total_Collection @;
 1801              put EMI_Collected @;
 1802              put Peak_Bucket @;
 1803              put As_ON_Date_Bucket @;
 1804              put Min_Bucket @;
 1805              put Final_Summary $ @;
 1806              put Recovery @;
 1808              put SystemCollection_POS @;
 1809              put Agency_Code $ @;
 1810              put Agency_Name $ @;
 1811              put Trail_Status $ @;
 1812              put Roll_Forward @;
 1813              put GAP @;
 1814              put Count @;
 1815              put Concatenate_Map $ @;
 1816              put POS_Recovery_Cash @;
 1817              put POS_Recovery_DAC @;
 1818              put Bounce_Late_Recovery @;
 1819              put Presentation_Clearance @;
 1820              put Additional_Payment @;
 1821              put DAC_Recovery_Tag @;
 1822              put Cash_Recovery_Tag @;
 1823              put Presentation_Recovery_Tag @;
 1824              put Bounce_Recovery_Tag @;
 1825              put Concat_Recovery $ @;
 1826              put Total_Amt_Recovered_Ex_Penal @;
 1827              put Total_Amt_Recovered_MIO @;
 1828              put Recovery_Percentage @;
 1829              put Recovery_Mode $ @;
 1830              put Final_Product $ @;
 1831              put HFC_CFL $ @;
 1832              put Recovery_Product $ @;
 1833              put GCL_POOL $ @;
 1834              put Product_BOM $ @;
 1835              put Product_BOM_EOM $ @;
 1836              put Product_BOM_MIN $ @;
 1837              put GCL_RISK_Perc @;
 1838              put EXISTING_PROV_Perc @;
 1839              put PROV_RELEASE_Perc @;
 1840              put MIN_PROV_RELEASE_PERC @;
 1841              put GCLFLOW_System @;
 1842              put GCL_Opening_Risk @;
 1843              put Existing_Prov_Held_Amt @;
 1844              put ProvRelease_System @;
 1845              put NCL_Syatem @;
 1846              put SaveFLow_System $ @;
 1847              put Actual_BKT $ @;
 1848              put Final_Bucket $ @;
 1849              put Final_product_NEW $ @;
 1850              put Call_Center $ @;
 1851              put ProductCityFinal_BKT $ @;
 1852              put EMP_Code1 @;
 1853              put ACM_EMPLOYEE_CODE1 $ @;
 1854              put CM_EMP_Code $ @;
 1855              put ACM_EMP_Code $ @;
 1856              put Final_Emp_Code $ @;
 1857              put Final_ACM_EMP_CODE $ @;
 1858              put Total_Amt_Recovered_Ex_Penal_MIO @;
 1859              put Recovery_NCL @;
 1860              put CM_Name $ @;
 1861              put ACM_Name $ @;
 1862              put Roll_BACK_POS @;
 1863              put NORMALISED_POS @;
 1864              put OUT_OFF_30_POS @;
 1865              put STAB_POS @;
 1866              put Roll_Forward_POS @;
 1867              put Write_Back @;
 1868              put WRITE_BACK_POS @;
 1869              put Cured_POS @;
 1870              put Cured @;
 1871              put Count_Roll_BACK @;
 1872              put Count_NORMALISED @;
 1873              put Count_OUT_OFF_30 @;
 1874              put Count_STAB @;
 1875              put Count_Roll_Forward @;
 1876              put Count_WRITE_BACK @;
 1877              put Count_C_OFF ;
 1878              ;
 1879            end;
 1880           if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 1881           if EFIEOD then call symputx('_EFIREC_',EFIOUT);
 1882           run;
 
 NOTE: The file '\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\PF_FINAL_14NOV17.CSV' is:
       Filename=\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\PF_FINAL_14NOV17.CSV,
       RECFM=V,LRECL=32767,File Size (bytes)=0,
       Last Modified=14Nov2017:12:13:16,
       Create Time=14Nov2017:12:13:16
 
 NOTE: 930408 records were written to the file '\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\PF_FINAL_14NOV17.CSV'.
       The minimum record length was 479.
       The maximum record length was 1803.
 NOTE: There were 930407 observations read from the data set SASVACL.PF_FINAL.
 NOTE: DATA statement used (Total process time):
       real time           39.05 seconds
       user cpu time       36.02 seconds
       system cpu time     2.23 seconds
       memory              10408.10k
       OS Memory           36716.00k
       Timestamp           11/14/2017 12:13:55 PM
       Step Count                        89  Switch Count  1137
       
 
 930407 records created in \\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\PF_FINAL_14NOV17.CSV from SASVACL.PF_FINAL.
   
   
 NOTE: PROCEDURE EXPORT used (Total process time):
 NOTE: "\\\\10.4.1.197\\CollectionAnalysis\\Karthik\\PF_FINAL\\PF_FINAL_14NOV17.CSV" file was successfully created.
       real time           39.19 seconds
       user cpu time       36.08 seconds
       system cpu time     2.30 seconds
       memory              10408.10k
       OS Memory           36716.00k
       Timestamp           11/14/2017 12:13:55 PM
       Step Count                        89  Switch Count  51
       
 
 1883       
 1884       
 1885       
 1886       
 1887       OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;
 SYMBOLGEN:  Macro variable _WEBVVN resolves to V7                                                                                   
                                                                                                                                
 1899

SAS Innovate 2025: Register Now

SAS Training: Just a Click Away