05-11-2015 03:02 PM
Our department is moving all the current SSIS/SSRS processes that are creating bunch (~50) of reports to SAS grid. The total data is not much (~40GB). It is easy to move the data from SQL server to SAS, but the daunting task is to create the similar reports in SAS. We can't use SQL pass through because SAS grid will be used as DW after the move.
Currently, the plan is to look at each and every SSIS code and recreate the entire logic in SAS. However, it is going to be take months to recode everything (70+ processes) in SAS and testing them successfully.
Is there any easy way to make use of some of the powerful ways SAS can perform all the manual task and automatically validate the report?
I would greatly appreciate if someone can provide ideas to move this faster.
05-11-2015 03:06 PM
Doubt it. You're going to end up doing a very manual process.
The only thing I would recommend is making sure you have someone who is well versed in both SAS and T-SQL to make sure SAS is being used efficiently. Sometimes recreating a process may not be the same as simply re-writing SQL. Also, if reports are using specializes summary/cube functions there may be ways in SAS to optimize those calculations.
What are you using in SAS to generate reports - Web Report Studio or Base SAS?
05-11-2015 03:15 PM
It would be nice if SAS can have some options in PROC SQL (like feedback) to print the relative SAS code in log when we do a pass through.
That is what I was kind of looking everywhere.
Currently, only ETL processes are being moved. We will end up using WRS and information delivery portal.
I have used Proc Report and emailing method before, but not the WRS, which does not seem like hard.
05-11-2015 03:57 PM
I just remembered, Isn't it the "sastrace" option can be used to bring in the actual code used in the pass through?
Can I able to advance that option with some other option to come up with what I wanted?
05-11-2015 05:13 PM
When you do pass through execution there is no code conversion, the code is 'passed through' to the server. This is why the pass through code has to meet the SQL standards and isn't SAS code.
Even if you could use sastrace it wouldn't help with the code conversion.