BookmarkSubscribeRSS Feed
oneminute
Calcite | Level 5

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?

8 REPLIES 8
SASKiwi
PROC Star

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.

oneminute
Calcite | Level 5

Thank you this is exactly what I wanted to know.

Reeza
Super User

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.

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
oneminute
Calcite | Level 5

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

ballardw
Super User

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

jakarman
Barite | Level 11

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

---->-- ja karman --<-----
oneminute
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2028 views
  • 12 likes
  • 5 in conversation