08-27-2013 11:04 AM
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
08-27-2013 01:16 PM
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.
08-27-2013 02:30 PM
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.
08-27-2013 02:50 PM
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.
08-27-2013 03:06 PM
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.
08-27-2013 03:49 PM
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.
08-28-2013 11:07 AM
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.
08-28-2013 12:05 PM
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.
08-27-2013 01:20 PM
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.