topic Re: SQL vs SAS in SAS Enterprise Guide

SQL vs SAS

oneminute — Wed, 29 Jul 2015 01:00:13 GMT

A general question as to whether SQL can do most of what SAS can do as far as data pulls and data manipulation. More specifically, can SQL replicate the FIRST. LAST. functionality of SAS?

Re: SQL vs SAS

SASKiwi — Wed, 29 Jul 2015 02:47:21 GMT

In a word no. SQL can't do FIRST. LAST. type processing.

SQL and the DATA step each have their strengths and weaknesses. Which might be the better option depends on what you are trying to do. I use both - remember both are still SAS if you are using PROC SQL.

I tend to use SQL when I want to do lots of table joins with varying join criteria, but then I switch to the DATA step when I want to do complex conditional processing. With SAS you have the advantage of choosing the best tool for the job.

PS: You can sort of fudge FIRST. LAST. type processing in SQL if you get into SQL cursor processing (available in Oracle, SQL Server and other relational databases), but its really ugly compared with SAS's simplicity.

Re: SQL vs SAS

Reeza — Wed, 29 Jul 2015 03:49:55 GMT

Data transposes are harder in SQL, not impossible, but harder, especially depending on the flavour/version of SQL you're referring to.

When you're doing analysis row by row it's much much much easier in a data step.

Re: SQL vs SAS

jakarman — Wed, 29 Jul 2015 05:58:05 GMT

The SAS data_step is the only part you are taking into account for comparing SQL/SAS? That is very limited one.
SQL was invented for OLTP and it has the limitations and advantages involved for that. SAS was invented for analysis with those limitations and advantages.

You can do al lot in the SAS datastep (pointering) that is not possible with SQL due to the fact SAS is processing all the data sequential and SQL is doing it in subsets. The performance behavior is also totally different.

Re: SQL vs SAS

ballardw — Wed, 29 Jul 2015 15:18:14 GMT

And to throw a wrench in the data manipulation side SAS has Proc IML for matrix manipulation.

Re: SQL vs SAS

jakarman — Wed, 29 Jul 2015 16:25:39 GMT

Ballard and the HASH package within DS2. Many procs that could be seen as packages and yes with IML you could use R packages .

Re: SQL vs SAS

oneminute — Wed, 29 Jul 2015 18:41:10 GMT

I am not familiar with IML. Somewhat familiar with HASH. Thank you so much for your answers.

Re: SQL vs SAS

oneminute — Wed, 29 Jul 2015 18:42:37 GMT

Thank you this is exactly what I wanted to know.

Re: SQL vs SAS

oneminute — Wed, 29 Jul 2015 18:43:38 GMT

No, but I am working with somewhat novice users and it is all they will probably care about at this point.