SAS Oracle Pl SQL Developer

Role: SAS Oracle Pl SQL Developer

Location: New York NY

Duration: 6 Months

Project Description:

Consultant who will be responsible for converting SAS code into Oracle database Tables, views, procedures, packages and functions. They also will be helping to create, design, database tables write packages, stored Procedures, and functions with industry standard to make sure queries running fast.

Scope of Work:

The work will include but not limited to:

• Consultant will convert (re-develop) existing SAS code-based Backend process into an optimized Oracle PL/SQL codebase.
• This backend process integrates multiple data-sources, massages data, implements business logic & business rules, and prepares data for final reporting.
• Consultant will ensure the converted code maintains the functional equivalence or better and performance efficiency of the original SAS code.
• Overall, the script does not perform complex conversions using SAS statistical functions, it is an ETL process written in SAS that can be transitioned to PL/SQL. ACS was able to convert 15% of the code and did not encounter any technical issue, Client needs consultant to assistance due to resource constraints with internal staff.
• There are about 120 SAS code snippets couple of sample snippets are as below.
• Sample SAS code

1. Sample 1

data facspell; set facspell; by vid ;

if not last.vid then do ; p = _n_ + 1 ;

set facspell (keep=start_dt rename=(start_dt=end_dt)) point = p ; end ;

else end_dt ='01jan2111'd ; /*future date*/

if first.vid then f_act='F000';run; data fac; set fac(drop=end_dt); by vid start_dt;

if not last.vid then do ; p = _n_ + 1 ;

set fac (keep=start_dt rename=(start_dt=end_dt )) point = p ; end ;

else do; end_dt ='01jan2111'd ;end; /*future date*/

;run;

2. Sample 2

data FO (drop= moda rename=N=P); set FO ;by recipid; N = _n_ + 1;

if act not in ("M981" "M982") then facid=moda; else facid=modb; if not last.recipid then do ;

p = _n_ + 1 ;

set FO (keep=actdate act rename=(actdate=actend act=nextact )) point = p ; end ;

else do; actend ='01jan2111'd ;nextact=''; end; /*future date*/

*TEMPORARY actend for M983 purposes;

if act in ("M999", "M990", "M970", "M950") then actend= actdate;

;run;

Detailed Requirements: Analysis and Assessment:

• Conduct a thorough analysis of the current SAS code, including identifying all macros, data steps, procedures, and any external dependencies.

Design and Planning:

• Develop a detailed technical project plan outlining the steps for the conversion process, including timelines and milestones.
• Pre-Approval of any design changes required from ACS development team

Code Conversion:

• • Rewrite the SAS code into Oracle PL/SQL, ensuring all functionalities are accurately translated and optimized for Oracle's architecture.

Testing and Validation:

• • Conduct comprehensive testing to ensure the converted PL/SQL code produces the same results as the original SAS code. This includes unit testing, integration testing, and user acceptance testing (UAT).
  • Parallel testing needs to be done. Execution of SAS code and execution of Oracle PL/SQL code should produce identical results.

Performance Optimization:

• • Optimize the PL/SQL code to ensure it meets or exceeds the performance of the original SAS code.

Documentation:

• • Provide detailed documentation for the converted code, including architecture diagrams, data flow diagrams, and code annotations.

Training and Knowledge Transfer:

• • Offer training sessions and materials for the client's development team to ensure they are proficient in maintaining the new Oracle PL/SQL codebase.

Support and Maintenance:

Provide one month of post-implementation support and maintenance to address any issues that may arise and ensure smooth operation.

Candidate Qualifications

Experience and Expertise:

• • Proven track record of successfully converting SAS code to Oracle PL/SQL in similar projects.
  • Deep understanding of both SAS and Oracle PL/SQL, including their respective strengths, limitations, and best practices.
  • Experience with performance tuning and optimization in Oracle environments.
  • Familiarity with data warehousing, ETL processes, and database management.

Technical Skills:

• • Proficiency in SAS programming and Oracle PL/SQL development.
  • Expertise in SQL and understanding of relational database concepts.
  • Knowledge of data modeling, database design, and schema creation in Oracle.
  • Ability to use Oracle development tools and utilities effectively.

Project Management:

• • Demonstrated ability to manage projects, including planning, execution, and delivery within agreed timelines and budgets.
  • Strong problem-solving skills and ability to troubleshoot and resolve technical issues promptly.

References and Past Performance:

• Provide references from previous clients for whom similar SAS to Oracle conversion projects have been successfully completed.
• Case studies or documented success stories showcasing the vendor's expertise and successful project outcomes.
• Training and Support Capabilities:
• Ability to provide comprehensive training and knowledge transfer sessions for the client's team.
• Availability of support services post-implementation to address any issues and provide necessary updates or improvements.

Compliance and Security:

• • Adherence to industry standards and best practices for data security and compliance.
  • Experience in handling sensitive data and ensuring compliance with relevant regulations.