Convert SAS Code to SQL

[hartley]

Hi

I was wondering if someone could please help me convert the following SAS code to work in SQL?

Many thanks

For some reason it looks like the full code hasn't been posted so I have attached the code as well

data AmortisationModel_02 (drop=MthlyPmt MonthlyPayments i);
set AmortisationModel_01;
if AvgPmt lt 0 then MthlyPmt = 0;
           else;
array Payments{132

[jklaverstijn]

Hmm I wish you good luck on this one. I am not going to solve this for you but a quick glance made me think you are maybe better of if you reorganize your data and transpose to go from var1-var132 in one obs to 132 obs of only the sequence nbr and var. SQL does not do this array stuff very well.

Also keep in mind that SUM() behaves different between datastep and SQL.

SAS SQL has a great implicit way of comparing row values to group statistics so that after transposing and doing some clever GROUP-BY's you may be out of the woods.

Hope this helps,

- Jan.

[LinusH]

Is there any problem with the current program? Why do you want to convert it to SQL? 

[hartley]

Unfortunately the department that I have gone to do not use SAS only SQL so I need to re-write it 😞

[Kurt_Bremser]

So they are people who make a trip to the US with the intention to not speak one word of English?

Forgive my pointedness, but ...

Keep in mind that the DATA step language is the native tongue of SAS and carries much more power than SQL (SQL has other merits, so one uses it when appropriate), and SAS even added to that with PROC DS2.

Forcibly switching to SQL only makes sense if you are abandoning SAS as your BI platform, in all other cases it is utter foolishness, IMHO.

[LinusH]

Even if I can sympathize with your situation, you'r not likely to get help from this community to phase out SAS on your site...

Tell your department management that they might need to open their wallets to get this migration done.

[RW9]

I would just add my commments to what @jklaverstijn has said.  SAS allows you to process the data in either transposed - which is what your data is (i.e. across the page) - and normalised (i.e. down the page).  SQL however relies on a Relational DAtabase Model, this is normalised tables (not one, but several), with values only held once and linked between the tables.  Your first step would be to create your database using your SQL database software, i.e. create the schema (document which describes each table and their links between them).  Then you would migrate your data into this new strcuture on the database.  Then you would identify the code which needs to be migrated, using the specs for your current code you would develop a migration plan, a Functional Design Specification etc. and rebuild the code in your desired application.  This involves a fair few different disciplines.

As for your code, it is not directly translatable into SQL because of the fact it is working across tables, rather than down columns.

[hartley]

Thank you for your reply, would a soultion be easier if the data where not to be transposed?

Many thanks

[Kurt_Bremser]

If you're really into ditching SAS, I think it would be better to create new apps in the target environment according to specifications, than trying to convert SAS programs, especially when those are quite complex.

So get back to the drawing board, look at your existing data, and use your new tools to achieve the required result. Simply converting the existing codes will only leave you with a big kludge.

[RW9]

Once you have migrated the data to the new system, you would then look at the problem in conjunction with the new data structure.  The syntax is different, i.e. you might use a cursor to go over the data, or you may use aggregates, or you may use joins or a combination of all of them.