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?
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.
Thank you this is exactly what I wanted to know.
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.
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.
No, but I am working with somewhat novice users and it is all they will probably care about at this point.
And to throw a wrench in the data manipulation side SAS has Proc IML for matrix manipulation.
Ballard and the HASH package within DS2. Many procs that could be seen as packages and yes with IML you could use R packages .
I am not familiar with IML. Somewhat familiar with HASH. Thank you so much for your answers.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.