Which is better, the DATA step or PROC SQL?

[saqi2000]

Which is better, the DATA step or PROC SQL? When should you use PROC SQL in preference to the DATA Step, and vice versa?

 

[MatthiasB]

Hi Saqi,

in general I find DataStep faster and more flexible than SQL, especially when you go for more complex queries. You have more control how you join, merge, or setkey the tables, whereas the SQL is mainly cartesian products (can be huge).

Cheers,
Matthias

 

[saqi2000]

Matthias,

Is it not possible to restrict the number of columns by using where statment. For example,

select empid,empname,empaddr1,empaddr2,zipcode,jobt
from employee
where jobt='Programmer' and empid <3000;

Thanks for your replies

Saqi

 

[JohnBlaze]

You could also use the fetch command to limit the amount of records returned when using proc SQL ( I believe)

 

[MatthiasB]

Hi,

you can limit in PROC SQL using INOBS=, e.g.

proc sql inobs=5;
   select * from sashelp.afmsg;
quit;

Another option OUTOBS= restricts how many observations will be created.

In DataStep it's similar, FIRSTOBS= specifies which obs is the first to read, OBS= specifies the last to read:

data limited;
   set sashelp.afmsg (firstobs=5 obs=10);
run;

That reads 6 observations (#5 to #10).

Cheers,
Matthias