Data Step Vs Proc SQL in SAS

sas
proc_sql
data_step
#1

Hi,

I am using SAS for more than one year and realize that various operations that I performed with data steps can also be done using proc sql statements like merging, sub-setting and many others.

Now I want to understand which of these two methods is best for which situation? Should we go with PROC SQL or DATA Step?

Regards,
Steve

#2

@Steve

SQL introduced with SAS after version 5. It was introduced to use some of the
powerful fuctionality of SQL. There is difference between these two methods like their terminology, process or functionality. In some cases, one is better than other.

Terminology differences

SAS                 SQL
Data sets          Tables
Observations       Rows
Variables          Columns
Merge              Join
Extract            Query

Reading Data Sets
There is difference is how data step and proc sql works with datasets. Data step reads record sequentially to program data vector, then does some processing with it and outputs it to data set whereas proc sql puts everything in memory and does all the calculations. After that it writes all the data to data set.

Differences between these methods

  1. Create Multiple Data sets:- DATA Step can create multiple data sets in one step whereas PROC SQL requires several SELECT clauses to create multiple data sets.

  2. Reading Text File: In Data step, you can read text file where as PROC SQL can’t.

  3. **Joining Table:**PROC SQL can join multiple tables those don’t have key variables in common where as Data step requires common named key of same format columns to merge. Data step can not provide cartesian product.

You can also refer below link:-

http://www2.sas.com/proceedings/sugi29/269-29.pdf

Regards,
Sunil