Subhash Chopra Email and Phone Number

Developed Weekly ETL process to extract and load Convenience Card data to Customer Information Warehouse (CIW) table. Development required reading source files from mainframe, perform data analysis, transform data and push a flat file to Unix for loading into Oracle database.

Environment: SAS EG, PC SAS, SQL Database, SQL Server Management Studio.• Worked on SAS process for SBB (Small Business Banking) LOC (Line of Credit) to convert to SQL Server application.

• Developed Daily ETL process for FATCA (Foreign Account Tax Compliance Act of USA) project by extracting customer data from Oracle database and storing in SAS datasets in Unix plateform. The data was then processed to identify clients that had links with a person or organization who had US Indicia. These clients were then put on a mailing list for FATCA package.• Developed an application to identify customers whose ID’s did not meet AML requirments.• Developed an ETL process to extract Card Numbers that had Aventura SSO Card to add a link for Single Sign on.• Devloped SAS application to create a CSV file (to be opened in Excel by user) that will provide counts of customers switching between paper and electronic statements.• Developed an ETL process to identify customers who made material changes to their personal info e.g. Name, address, phone number etc.• Developed an ETL process to prepare daily load files for CPS ( Customer Product System) by reading input files from the source systems.• Developed SAS programs to compare two databases by extracting data into SAS datasets and using Proc Compare to perform column by column comparison. This was done to ensure accuracy of database migration from non-stop (SQLMX) database platform to the Exadata (Oracle) database platform.• Developed an ETL process to load transaction data for the newly launched Prepaid Cards by CIBC.• Developed an ETL process to identify duplicated CIF numbers.• Developed SAS application for Consolidated and Reconciliation statements for use by core systems.• Investigated various queries from Customer Marketing and provided qualified answers to address concerns raised by retail and business customers.

• Worked as a team member of FWST (Firm Wide Stress Testing) project team responsible for developing SAS based solution for Basel II Compliance. The solution involved ETL, Data Mining, Data Warehousing and Reporting tasks using SAS tools such as ETL Studio, DI Studio, SAS OLAP Cube Studio, SAS Web Report Studio etc.• Developed over 150 Excel Reports showing EL (Expected Loss) and RWA calculations for Basel II compliance using Proc Report, ODS and ExcelXp tagsets.• Converted six Stress Testing models such as US Wholesale, HELOC etc. from Matlab to SAS using SAS IML.• Converted an Access Database comprising of over 300 queries to SAS. All queries were converted using SAS Proc SQL.• Performed various adhoc tasks related to data extraction, reporting and data manipulation.

• Developed and maintained SAS system to create Customer Sampling files utilized in VOCM (Voice of Customer Measurment) system that sends out survey invites to customers through Survey Companies such as Synovate and Maritz to provide feedback on their recent contact with American Express. VOCM system uses Unix as main plateform with interfaces to Mainframe and Windows FTP servers. Main tools used were Base SAS, SAS Macros and SAS Enterprise Guide.

• Developed SAS Datasets to load Clinical Trials Data from XML files to SAS data library. This project was suspended after development of POC was completed, due to some reorganization at Eli Lilly.

• Worked as SAS consultant in Capital Markets and Risk Management Group of BMO supporting and maintaining two of their applications APMS (Asset Portfolio Management System) and IBUK (International Banking UK). • Maintenance and enhancement of the APMS system that is 95% in SAS using Base SAS and extensive use of SAS Macros.• Monitoring daily and month-end ESP jobs and performing daily and monthly log checks of the jobs that load and ftp the data to and from NT/UNIX servers. Debug and apply fixes as necessary. • Liasoning with and training of offshore team located in Bangalore, India. • Also developing and maintaining FOCUS reports of IBUK (International Banking System). This system uses reporting features of FOCUS against the IDMS database where the application data resides.

Worked as a SAS resource to their team of SAS analysts and worked on various ad-hoc projects to convert SAS Datasets to sequential files and vice-versa. Also performed statistical analysis on some of the extracted data files to categorize customers into different marketing initiatives.

Developed SAS modules to replace existing Cyborg Generator programs to create reports and extract files from Payroll System. Using SAS ver. 8.2 on Windows 2000 plateform.

• Developed Module 3 for XEFS0002, one of the existing Report driver for ACDO system to allow user to be able produce a graph of any of the requested reports. Responsibilities included coding and testing of the program, prepare Test Cases and Test Plan in accordance with Computer Systems Validation Policy of Eli Lilly & Co., write complete documentation for the new module, co-ordinate with statisticians with respect to user needs and maintain supporting paper and electronic trail for FDA compliance.• Modified an existing SAS macro CGMFIX to work in both MVS and PC environments. Purpose of this macro is to correct errors in CGM output generated by SAS/GRAPH.• Developed a process using FILEAID, that would globally change the names of about 15 utility macros from X* to U* that were being referenced by ACDO macros and sub-macros. There were over 10,000 references to those macros and changing them manually was a very time consuming and error-prone task. Responsibility included developing necessary procedure and input file that will be read by FILEAID and producing paper report showing before and after picture for audit purposes.• Modified Report Driver XLAG0001 to allow user to produce graphs using CGMCLEAR driver.• In addition, I performed various tasks relating to Lilly’s transition from SAS version 6 to SAS version 8 including rewriting EFS2 for V8 and peer reviewing of other team members’ work.

• Worked on Patriot Project Phase II to set up Data Mining Environment on the UNIX platform.This project required extensive use SAS MACROS, BASE SAS and FOCUS.• Worked on Blitz Project involving system changes relating to Year 2000 and new NPA ( Area Code 780 for Northern Alberta). • Developed Update Process to apply corrections to the Billing Files for some billing rounds where amounts for Your Way Unlimited Plan were not reported correctly.• Worked on Patriot Project Phase I and developed Customer Segmentation System. This phase of the project was aimed at assigning Customer Segmentation values to each customer based on each customers current and potential value to the corporation. • Assisted in Database design and Data Extraction process for phase II of the Patriot Project. This phase was an extension of Phase I on a much larger scale. This phase also include provision for Data Mining by Marketing Divisions of the company. • Developed Microsoft Access Macros and Queries to load Corporate Process Integration Data into the central database. • As a member of Y2K Change Team, analysed over 500 Programs and JCLs to identify changes required for the Year 2000. • Developed interface jobs and programs for OFC system to feed information to the new SAP system to be introduced January 1st, 1998. • Converted all existing data to make it Y2K compatible. • Modified CSBI (Customer Service Billing Inquiry System) to make it Y2K compatible and to load and report on additional data coming from OFC system. • Developed Access Database to keep track of systems interdependencies of over 100 affected systems within Telus. • Streamlined various kinds of data loads coming from nine subsidiary companies of Telus to convert them to a common format. • Developed SAP interface for AFFMI system (Fleet management System).