Some useful tips on proc sql

PROC SQL (Structured Query Language) is a powerful data analysis tool. It can perform many of the same operations as found in traditional SAS code, but can often be more efficient because of its dense language structure.

JOINING THREE OR MORE TABLES

PROC SQL can be an effective tool for joining data, particularly when doing associative or three-way joins. Performing this operation using traditional SAS code would require several PROC SORTs and several DATA step merges. The same result can be achieved with one PROC SQL. For example, we need to join three tables viz. CUSTOMER, SALES and RETURN and where return > 10.

The syntax for getting the table:

PROC SQL; create table customer_data as
SELECT B.Cust_F_name, B.Cust_L_Name, B.return, 
E.id, F.STATE, F.Sales
FROM Return as B, customer as E, SALES as F 
WHERE B.Cust_F_name=E.Cust_F_name AND 
B.Cust_L_name=E.Cust_L_name AND 
E.id=F.id AND 
return > 10; 
QUIT;

How to use drop/keep in proc sql:

One can use drop and keep within proc sql. Here is the syntax:

proc sql; 
create table Scores1 as 
select * 
from customer_data(drop=Cust_L_name Cust_F_name); 
quit; 

Proc sql can be used for creating macro value list also. For more details please refer the following link:

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