Convert SAS Code to SQL

Reply
New Contributor
Posts: 3

Convert SAS Code to SQL

[ Edited ]

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

 

 

 

Attachment
Super Contributor
Posts: 406

Re: Convert SAS Code to SQL

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.

Esteemed Advisor
Posts: 5,198

Re: Convert SAS Code to SQL

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

Data never sleeps
New Contributor
Posts: 3

Re: Convert SAS Code to SQL

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

Esteemed Advisor
Posts: 6,646

Re: Convert SAS Code to SQL

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 5,198

Re: Convert SAS Code to SQL

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.

Data never sleeps
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Convert SAS Code to SQL

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.

New Contributor
Posts: 3

Re: Convert SAS Code to SQL

 

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

 

Many thanks

Esteemed Advisor
Posts: 6,646

Re: Convert SAS Code to SQL

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Convert SAS Code to SQL

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.

Ask a Question
Discussion stats
  • 9 replies
  • 829 views
  • 3 likes
  • 5 in conversation