Need help converting a SAS code to oracle SQl procedure

[adityareddy]

Hi,

My name is Aditya.

I am trying to convert a SAS code to SQL procedure.

Since i am not familiar with SAS can any one help me to convert the code to SQL procedure

thanks in Advance

[DBailey]

I think you'll need to provide more details.

[adityareddy]

Yes i can provide more details.

i can attach the SAS program.

Before that the overview of the SAS code is. the code is using 5 input files and the final result has 3 output files.

How can i Upload the file. can i sent it to your email.

[esjackso]

If you switch to the advanced editor (upper right of the post box) the attach box will be at the bottom.

EJ

[esjackso]

Well I think converting 1200+ lines of code may be a little much to ask of the volunteer forum.

Some the sql appears to already be written in places (commented out with *s) but some of the data steps will be hard to convert because of the processing that SAS enables through the data step.

Probably going to have to find a SAS consultant to help rewrite if no one at your organization does SAS.

EJ

[adityareddy]

Yes, I know its a lot to ask.

But i have one question is it worth converting this SAS code to a SQL procedure.

and Is it possible for you to covert the code to SQL procedure, or for any one else who you know.

If you can tell me how much time it will be taking to do the conversion it would be great.

Thank you

[esjackso]

I dont think we have enough information to help answer those questions.

If you have SAS available and the code was written for your specific situation, it isnt hard to run SAS code. The problem would be updating it if requirements changed.

If this is just example code where conditions, table names, variables etc ... could all be different it might be easier to start from scratch with Oracle and just duplicate the requirements.

You could probably find a SAS consultant that could outline each step in the program and describe what is going on so someone can duplicate that in Oracle. I couldnt even fathom a guess about how long that would take.

EJ

[adityareddy]

Thank you, that was helpful.

I will try to work it out.

can i contact you further if i have any other questions.

[adityareddy]

Actually this is the actual code which i have to duplicate it into a SQL procedure. i am a PL/SQL developer.

[Reeza]

From a  very quick glance the code does a few things which may be a pain in the a** to do in sql:

1. Generate multiple tables from one data step

2. There are proc summary runs so the definition of the stats may not match the sql implementation (unlikely, but possible)

3. There's a few macros, but you can probably convert that somehow to a stored proc

4. There's also a few imports from Excel so you'd need the SSIS processes for those, in MS SQL terms.

5. There's also direct exports to excel so you'd need the procedure for those.

One other thing, in my experience for code like this, you'll find in the transition process that not all the business rules are applicable at the end or understood, so sometimes you need business knowledge in addition to SAS.

Good Luck.

[adityareddy]

Hi Reeza,

I am wondering is it possible to convert the CODE to a SQL Procedure and is it worth doing the conversion.

[jakarman]

Hi adityareddy, I have seen the code also as quickscan. As reeza and esjackso have been indicated ... challenging
There is a lot of logic in it not knowing what is intention is is very hard to convert to PL/SQL could even not being possible.

It is a SAS language you are asking to convert to an other language. Normally they are cooperating. Proc SQL is the cooperation example.

Do you have the Functional design?  

That could/should give some answer.,

Would you have asked: I have a PASCAL program can it be converted to SQL? It would be more clear to see the logical problem.

Every language has something special. As SAS is many times used for simple dataprocessing sometimes it is possible to do something to go for SQL way. Mostly SAS is connected to DBMS systems to solve the issues there that are not covered. 

[Reeza]

Yes it's possible (anythings possible).

Is it worth the time, probably not. The costs of hiring SAS programmer to make changes/fixes incrementally are probably less than the cost to have the conversion done. However with the conversion done you can make changes more easily "in house" so the cost-benefit analysis is really an internal business discussion more than a dollar discussion. The overall cost would be less than 200 hours I'd guess, so 200hours *$150hour for a decent consultant = $30,000 at minimum.  Cost of being to update in future without requiring a consultant = priceless . 

*prices are random guess.

[esjackso]

Can you provide the code you trying to convert? Is it data step processes (steps that start with Data) or proc sql steps?

SQL steps should translate pretty well ... data steps may or may not translate directly depending on what is occurring. Then if their are other procs in the code then it will really depend on which procedure it is and what is being done.

EJ

[adityareddy]

Most of the code Start with DATA.

they are a couple which are proc sql.