Data Sql
Resume:
Ravi
[pic]
******@*****.***
Summary:
. Certified BASE SAS Programmer with over 7 years of experience in
developing Statistical Analysis and reporting applications in
financial,Insurance,Electric industry using SAS Software
. Extensive experience in SAS/BASE, SAS/STAT, SAS/SQL, SAS/MACROS,
SAS/GRAPH, SAS/ACCESS, SAS/ODS, SAS/CONNECT
. Extensive programming experience with Proc SQL, Proc Report, Proc
Freq, Proc Means, Proc Tabulate, Proc Transpose, Proc Gplot,
Proc Gchart, Proc Datasets, Proc Summary, Proc Format, Proc
Compare, Proc Append, Proc Import, Proc Export, Proc Printto, Proc
Print, Proc Upload, Proc Download procedures
. Experience in developing SAS procedures, macros, and applications
for data cleaning, programming, reporting and documentation
. Expertise in querying and loading data on DB2, Oracle,Teradata
database systems
. Experienced in Data management, data extraction, manipulation,
validation, purification and analyzing large sized data.
. Extensive experience with merging and concatenating and
interleaving SAS datasets
. Excellent skill in building SQL queries using various joins and sub-
queries on various database systems, tuned for better performance
and efficiency
. Experience in using DB2, MS Access, Excel, SQL Server, and Oracle
. Expertise in using advanced functions in MS Excel like Pivot
Tables, VBA macros, Financial Functions, Statistical Functions,
Data export/import etc
. Proficient at SAS/ODS for delivering output in different formats
including RTF, HTML and PDF
. Have experience as a SAS programmer in UNIX and Windows Environment
. Experience in problem solving and delivering practical solutions
. Profound oral and written communication skills
. Able to work as an individual or in a team of any size
TECHNICAL SKILLS:
Statistical Software: Base SAS, SAS/STAT, SAS/MACROS, SAS/SQL, SAS/GRAPH,
S/ACCESS, SAS/ODS, SAS ETS, Enterprise Guide, ETL Studio.
Programming Languages: C, C++, SQL, PL/SQL, Visual Basic, Java, SUN-
Solaris
Databases: MS Access, Oracle, SQL Server, DB2,Teradata
Software Packages: MS Office (Word, Excel, PowerPoint), MS Project 2000,
Microstrategy,
TOAD, SQL Navigator, Cognos impromptu,
Crystal reports
Operating Systems: Windows 9x/NT/2K/XP, DOS, UNIX
Web Related: HTML/DHTML, CSS, Java Script, VBScript, Front
page, Adobe Photoshop,
Dream Weaver
Work Experience:
Bank of America, Newark, DE SAS Programmer/Analyst Jun
09 - present
The project mainly deals with credit card act of government regulations
forming rules to prevent unfair practices and named it as UDAP (unfair &
deceptive acts of practice). Worked with Technology Integration team which
supports ICM to apply the changes to credit cards and validate them
accordingly
Responsibilities:
. Analyzed Business Requirement Document, LLD's and HLD's of both
client systems from technical and business perspectives for different
phases of card act release
. Created SAS programs for validation of data on mainframe for Gen 1 and
Gen 1.5 release of CARD ACT(UDAP) changes
. SAS Validation programs include different changes to pricing systems
that are impacted by the card act.
. Added test plans in HP Quality Center and then added tests scripts to
the test set.
. Worked on Dartnet which automates the Data preparation/validation
process. It is a user-friendly interface that enables search against
all test accounts within the release database and querying on any
profile field.
. Extensively used SAS macros to automate the validation programs to run
on large amount of data on mainframe
. Used PROC EXPORT to export the datasets into excel reports with given
format as per the users and also used different SAS procedures like
freq, means, report, sort, compare
. Used IBM Mainframe application, CICS, Customer Information Control
System which is a transaction processing system designed for both
Online and Batch activity.
. Used and maintained mainframe CICS applications via 3270 emulation to
support, development, testing and production implementation.
. Involved in Functional testing, Unit testing and Regression testing
running SAS programs.
. Used Excel for Graphs and Charts, Macros, Pivot tables, Function and
Formulas etc
. Involved in finding defects and re-tested it to confirm the fix
through Quality center.
. Worked in testing environment for testing the accounts with test data
and in Production environment for testing the accounts with real data
using SAS validation programs.
. The bulk changes applied in pricing like interest rates, fees are done
by VIMASS and CASG bulk mainframe SAS program file with certain file
layout and goes through FM process.
. Validated the accounts which goes through different phases of Pricing
processes like, FM (File Maintenance), Blaze(Exclusion Rules),
TPPC(Triad Pricing Parameter Control), TRIAD(Strategy decision
Engine), Pset (pricing set) and VI(variable Interest)
. Worked in TSO (Time Sharing Option) and ISPF (Interactive System
Productivity Facility) software environments and were able to use
IBM's SDSF (System Display and Search Facility) to control and monitor
batch jobs.
. Worked on SAS/JCL on mainframe to assign memory for jobs and the
datasets
. Used FTP (File Transfer Protocol) to transfer files across platforms.
Environment: SAS 9.1.2, IBM Mainframe SAS, SAS Enterprise Guide,
SAS/CONNECT, SAS/SQL, DB2, QMF, Windows NT, Mainframe FILEAID, HP Quality
center
Independence Blue Cross, Philadelphia, PA SAS Developer
Jun 08 - Apr 09
The project deals with the Physician group performance reporting which
includes Clinical quality measures, Prescription drug utilization and Cost
of care performance data for the providers and also for the hospitals
Jefferson, Abington and Women's health group within Philadelphia area
Responsibilities:
. Involved in replatforming the code for applications that moved from
Managed care data mart (MCDM) database to Active Enterprise data
warehouse (AEDW) on Teradata database.
. Used Teradata Sql assistant to query against AEDW database for the
physicians performance data.
. Applied Benefit adjustment for different specialties like
Obstetrics/Gynecology, cardiology and endocrinology areas to get the
cost of care data by each provider and specialty.
. Depending on different health care plans and the providers the illness
burden score and risk adjustment are calculated for facility,
management, surgical, pharmacy, ancillary buckets
. The prescription drug utilization is calculated by getting the
pharmacy claims data for the providers and summarizing by physicians
group, brand and generic prescription drug rates and prescription
costs by specialty.
. Used Proc means to calculate the mean, median of the generic scripts
percentage to overall peer providers generic rate
. Used Base/SAS, Proc SQL for converting the queries from MCDM to
Teradata on SAS and making datasets
. Applied the calculations to the Jefferson, Abington and Women's health
group for the Cost of care and the prescription drug utilization using
proc means
. Created macros in SAS for the specialty area, provider, state and the
type of report so that similar calculations can be used for different
specialty areas.
. Used Proc Datasets to delete the temporary datasets that are formed
during the changes in the development of the code
. Used PROC EXPORT to export the datasets into excel reports with given
format as per the users and also used export tab in SAS to specify the
destination where the file to be stored.
. The developed code is undergone User acceptance testing and checked
for the data for a period of time by comparing the results of MCDM
against AEDW data.
. The data is extracted to SAS from Teradata connecting to UNIX server
through remote submit and using SQL pass through the data is accessed.
. Used Proc Freq, Proc Sort, proc print, proc export, proc download and
proc upload SAS procedures for getting frequency, sorting the data,
printing the results in the output, exporting the results in desired
format, downloading the datasets from different libraries and
uploading the datasets respectively.
. So the reports helped the users for auditing the statements, fixing
the errors and changing the fields wherever typos were found
. The SAS development is done on PC platform and the developed code is
placed in the network drive where it is accessed by users for testing
and also for production
Environment: SAS 8.2, SAS 9.1, SAS Base, SAS Macros, SAS/ACCESS,
SAS/CONNECT, SAS/SQL, Teradata, Windows 2000, Teradata Sql assistant,
Microsoft ACCESS, MS Sql server, MCDM database
Bank of America, Wilmington, DE SAS Developer
Sep 07- Jun 08
The project Marketing Optimization revenue engine mainly deals with the
promotional offers from credit cards from Marketing product unit and also
the auditing reports for collateral management with Product offering engine
Responsibilities:
. Involved in writing the code for applications that moved from Informix
database to DB2 database. The queries output have been validated with
the output that has been produced by previous queries.
. Worked with business users getting the requirements and designing,
development of the code to meet the requirements.
. Used IBM DB2 connect to convert the queries in Informix to DB2
. Used Base/SAS, Proc SQL for converting the queries from DB2 to SAS and
making datasets
. Worked on Audit reporting for Infrastructure collateral management by
interacting with the business users.
. Used Data _null_ to generate user-defined date time function macros
and had a check on not creating new datasets
. Used Proc Datasets to delete the temporary datasets that are formed
during the changes in the development of the code
. Created MACROS to run the code for getting the output with no records
created in the output excel
. Used PROC EXPORT to export the datasets into excel reports with given
format as per the users
. The developed code is undergone User acceptance testing and the data
is mocked to check for the error types that were requested
. The data is extracted to SAS through SQL pass through facility and the
data is manipulated by different edit checks
. The data is extracted to SAS from Mainframe connecting to mainframe
server through remote submit and using SQL pass through the data is
accessed.
. Created Macros for comparing different fields of two tables to check
for any discrepancy in the attributes of two different banks
. Used crystal reports to send the audit reports in the form of excel
sheets and in pdf formats
. Used Proc Freq, Proc Sort, proc print, proc export, proc download,
proc upload SAS procedures for different purposes.
. The test plans are used to test the data in a proper sequence and the
queries were tested and were placed into production once confirmed
. The audit reports created for different areas that included Plastics,
Transparencies, Associate banks, Bank processes, statement control and
Global wealth infrastructure management.
. So the reports helped the users for auditing the statements, fixing
the errors and changing the fields wherever typos were found
. The SAS development is done on PC platform and the developed code is
placed in the network drive where it is accessed by users for testing
and also for production
Environment: SAS 9.1, SAS Base, SAS Macros, SAS/ACCESS, SAS/CONNECT,
SAS/STAT,
SAS/SQL, DB2, Windows 2000, IBM DB2 Connect, Microsoft ACCESS
PJM Interconnection, King of Prussia, PA SAS Developer
Jun 07- Aug 07
PJM Interconnection is a regional transmission organization that
coordinates the movement of wholesale electricity in about 13 states in US.
The project mainly deals with the market monitoring of the electricity
distribution. PJM's role as a federally regulated RTO and it acts
independently and impartially in managing the regional transmission system
and the wholesale electricity market
Responsibilities:
. Involved in developing the code for atomics and metrics by
understanding the business requirement
. The SCR (secondary change request) are prepared from the business
requirement where we plan what should be changed during the
development and the pending scr can be seen from PVCS client
. The job schedule info, control cycle table and audit log table are set
before running the actual developed program.
. The development of the code takes place as an update either as a
rolling or life cycle update and rolling update is mainly taken as 45
day cycle update and it changes every 45 days
. Used Base/SAS, Proc SQL and SAS/ Macros for extracting the data from
oracle and running queries on the data
. The bullet program is run which in turn invokes the macros that are
associated with establishing the sas libname for oracle database,
checking the changes for oracle table and also for control cycle and
audit log tables
. Used Data _null_ to generate user-defined reports and had a check on
not creating new datasets
. Used Proc Datasets to delete the temporary datasets that are formed
during the changes in the development of the code
. Used SQL Navigator and also TOAD to connect to the database through a
proper schema for the development server and run queries to check
every table that is developed has data
. Used Power docs software to access the Visio diagrams and also the
excel test plans which can be modified by all users that have access
for those documents.
. The Microsoft visio diagram flow is created for all atomics and also
metrics to explain what is taking place in the code.
. The test plans are written with description of test and the expected
results of that test and the actual results that are to be completed
by the tester
. The test plans are to be filled with sas log sheet and once the code
review is done, the testing is done by the testers
. Once the testing is verified the code is run in batch mode on testing
server and it is automatically run when the control cycle status is
kept as run mode
. The batch testing log has to be filled in the test plan document and
they have to be provided to the change request team for further
production process
. The SAS development is done on PC platform by connecting to CITRIX to
connect to sas software on different servers like development,
production and testing servers
Environment: SAS 9.1, SAS Base, SAS Macros, SAS/ACCESS, SAS/CONNECT,
SAS/STAT,
SAS/SQL, Oracle 9i, Windows 2000,CITRIX server for SAS, SQL
Navigator, TOAD, Powerdocs, Microsoft visio, PVCS client
Highmark Blue Cross Blue Shield, Camp hill, PA SAS Developer
Jan 07-May 07
Highmark Blue cross Blue shield is a Health insurance company and is an
independent license holder of Blue shield. The project mainly deals with
the development of report code for Integrated account reporting (IAR)
mapping the elements from Reports at Hand (RAH) which is a client-user
interface for reports generation.
Responsibilities:
. Involved in understanding the business requirement, documenting it and
then plan the required field variable values in the reports
. Reports at hand (RAH) is client-user interface from where a client can
get analysis reports of the claims paid for a single or two periods
. The Integrated account reports are obtained by calculations and
mapping the elements of Reports at hand
. Used Base/SAS, SAS/ Macros and Proc SQL for performing calculations
and mapping them with RAH elements on the given fields of interest
. Used Data _null_ to generate user-defined reports and had a check on
not creating new datasets
. Involved in edit checks using proc sort to delete duplicates with
nodupkey option and checked for invalid characters with Proc Freq
. The driver program to run the RAH reports is a headless program which
is used to check the diagnostics of the process.
. The run of headless invokes the macros code and subsequently datasets
are formed along with the running of reports
. The development code is placed in the original code and the headless
is run again for the generating new reports of IAR
. Developed SAS Macros for the current and prior period of the two
period analysis and then developed the code for the changes in final
code to standardize the report generation for re-use with PROC
TABULATE, PROC REPORT
. Interact with data management personnel as necessary to ensure that
datasets extracted from teradata are in usable format and lock the
data sets
. Controlled the memory by deleting the tables, formed by running
headless, using teradata administrator
. The final dataset contained one observation as per the requirement
with different variables having one value for each variable
. The headless is run on Unix through remote server by connecting to
mainframe emulator to ftp on IBM websphere client
. The developed code is stored on remote Unix directory and kept for
testing or code review
. Read data from teradata and stored as datasets in SAS. Developed SAS
code to clean the invalid data from the database.
. Used SAS/CONNECT to transfer and download datasets by connecting to
remote computers
. Testing and code review of the developed is done and checked for any
bugs or any other issues, errors
Environment: SAS 8.2, SAS Base, SAS Macros, SAS/ACCESS, SAS/CONNECT,
SAS/STAT,
SAS/ODS, Teradata 7.1, Windows 2000, UNIX, IBM websphere Host-on-
demand 9.03
Farmers Insurance, CA SAS Programmer Jan 05 - Dec
06
Farmer's is a leading Insurance agency in the areas of Auto insurance, Life
insurance, Home insurance, Term Life insurance, Fire insurance and
commercial insurance. The Data was built based upon various Databases
within the company. SAS is being used to maintain business data among all
divisions and produce reports.
Responsibilities:
. Involved in writing code using Base SAS to create SAS data sets by
writing various formats and informats and validated data to extract
from oracle tables.
. Created programs in SAS in the development of tables, listings, and
reports generation for Client Liaison.
. Experience with Data manipulating by MERGE, APPEND, CONCATENATION,
SORT and generated the reports based on the requirement.
. Experience using Base SAS (MEANS, FREQ, TABULATE, REPORT etc) and
other procedures for summarization, Cross-Tabulations and statistical
analysis purposes.
. Generated analysis reports, graphs using SAS/Reports and SAS/ODS to
produce HTML reports.
. Developed Macros for various instances for automating Data validation
by checking data distribution and by comparison to a standard data.
. Analyzed and interpreted numerical data from various sources to
organize and summarized data for analysis.
. Generated graphs using SAS/GRAPH and the SAS Graphics Editor.
. Extracted raw data from client files using SAS Import and created SAS
datasets using SAS/Sql pass through from Oracle database.
. Used SUBSTR function, and SCAN function, searched for strings using
INDEX function and created new formats using PROC FORMAT.
. Created ad-hoc reports and graphs as per the requirements of the
users. Used DATA _NULL_ and PUT statements to create the customized
reports.
. Successfully identified problems with the data, produced derived data
sets, tables, listings and figures that analyzed the data to
facilitate correction.
. Used Dynamic Data Exchange to acquire data from Excel spread sheets.
. Interacting with other team members and lead to discuss the required
developments to be made in coding to improve the functionality and
effectiveness.
Environment: SAS/BASE SAS/STAT, SAS/SQL, SAS/MACRO, SAS/GRAPH, SAS/ODS
SAS/ACCESS, Oracle 9i, SQL, MS Access, MS Excel,
Windows
Fifth Third Bank, Cincinnati, OH SAS Analyst Apr 03 - Dec 04
The Project objective is to design and develop Home loan trend analysis
prior to go for campaign. The required information is imported from legacy
systems, the historical reports are taken into consideration for campaign
plan. The reports generated help the loans department to keep track of
customer trends, repayment capacity, purchase pattern and segmentation of
customers accordingly.
Responsibilities:
. Data transfer and conversion from one platform to the other to be
used for further analysis(SAS to oracle, oracle to Excel)
. Used Dynamic Data Exchange (DDE) feature of SAS for importing and
exporting data between SAS and SQL.
. Created and maintained SAS Datasets that are extracted from an
Oracle Database
. Used SQL joins for improving the functionality of programs written
using merge techniques
. Create SAS data files and flat files for Risk and Model team in
windows environment, by collecting demographics and general
information of various card members pertaining to various card
products from existing data stores
. Used set and other data set concatenation techniques for combining
data vertically
. Data manipulating by MERGE, APPEND, CONCATENATION, SORT and
generated the reports
. Data validation by checking data distribution and by comparison to
a standard data. With proc compare validation of data is done.
. Used sql pass through facility and oracle dbload procedure for data
transfer
. Use various procedures like Proc Reg, Proc Univariate, Proc
Summary, Proc Freq, Proc Tabulate, Proc Report, Proc SQL, Proc
Sort, Proc Merge, Proc Gplot, Proc Gchart
. Code new SAS programs in UNIX and on PC SAS
. Used SAS/Base to perform sorting, merging and updating and
generated reports.
. Creating Summary Reports and Tabular Reports using Proc Report
. Wrote macros to develop a concise and reusable code
. Created HTML listings, tables and reports using SAS/ODS
. Wrote and modified SAS code in windows environment
. Extensively used statistical procedures like ANOVA, GLM,
UNIVARIATE, REG, MEANS, t-tests
. Generate, Compare and update the reports
Environment: SAS/BASE SAS/STAT, SAS/SQL, SAS/MACRO, SAS/GRAPH, SAS/ACCESS,
SAS/ODS, UNIX, Oracle, SQL, MS Access, Excel, Windows
[pic][pic]
Contact this candidate