PROC SQL - More Basic Examples

Go to Previous page  - PROC SQL FOR BEGINNERS - HOW TO USE SQL IN SAS

More Examples of Basic PROC SQL Programs

7. Getting Specific Number of Records From Dataset - Use of OUTOBS option

proc sql outobs=10; 
select *  From SASHELP.CARS;

Quit;

This Code is displaying First 10 records from SASHELP.CARS

8. Giving Label(New Display Name) to a Variable/Column in Dataset.

proc sql; 
select Make as Company, MSRP as Price From SASHELP.CARS 
where Make ='Audi';

QUIT;

This Code is changing Display name of Make to Company and MSRP to Price and Display all records for Audi

9. Creating New Variable in PROC SQL

Proc sql;
select Make, MSRP, (MSRP*0.10) AS DISCOUNT From SASHELP.CARS Where 
Make='Acura';

QUIT;

IT will Add New Variable in Display List named as DISCOUNT which is having 10% of MSRP.

10. If we want to Use Newly Created Variable in Same Select Statement then we need to use CALCULATED Keyword

proc sql;
select Make, MSRP, (MSRP*0.10) AS DISCOUNT, (MSRP-CALCULATED DISCOUNT) as FINAL_PRICE 

From SASHELP.CARS 
WHERE Make='BMW';

QUIT;

11. Removing Duplicates Using DISTINCT Keyword

proc sql;
select DISTINCT Make

From SASHELP.CARS ;

QUIT;

12. Multiple SQL Statements in One PROC SQL

proc sql;

select DISTINCT Cylinders From SASHELP.CARS ;

select Make,Cylinders From SASHELP.CARS where Make IN ('BMW');

QUIT;

13. Creating a New Dataset/Table using PROC SQL We can create New Dataset using CREATE TABLE statement in PROC SQL -

PROC SQL;

Create Table BMW as select Make,Cylinders From SASHELP.CARS where Make IN ('BMW');QUIT;

 

•