PROC SQL - BASIC COMMANDS

Under this section we will study working with Basic SQL commands –

• Create Table,

• Alter Table,

• Inserting data into Table,

• Update Table,

• Delete Records from the Table,

• Dropping Table etc.

Lets start with it one by one.

Creating Table with PROC SQL

A table can be created with 3 methods using PROC SQL.

Though according to syntax it is a table; however internally it is nothing but a SAS dataset itself.

Any table created using PROC SQL can be used with other data or PROC steps as a dataset outside the PROC SQL and any dataset created using data step or PROC step can be treated as table in PROC SQL.

There are three methods of creating a table using PROC SQL; those have been demonstrated with appropriate examples in the below section.

1. Creating table Using Create Table Statement.

SYNTAX:

PROC SQL;

CREATE TABLE tablename (columnname_1 DATATYPE <FORMAT formatname> <LABEL Labelname>, columnname _2 DATATYPE <FORMAT formatname> <LABEL Labelname>, columnname_3 DATATYPE <FORMAT formatname> <LABEL Labelname>,

………………………………);

QUIT;

SAS CODE:

proc sql;

create table emp

(empID int, empname char(200), deptID int, dob num format date9.);

describe table emp;

quit;

OUTPUT:

Output of this query can be observed in the log as follows;

create table WORK.EMP( bufsize=24576 )

(

empID num,

empname char(200),

deptID num,

dob num format=DATE9.

);

There are few points to make a note

Describe command displays a table structure inside the log file.

Though SAS has only 2 data type (numeric and character) one can use SQL specific (ANSI standard) data types like (int, varchar etc.)

In Addition one can also use the formats and labels within the PROC SQL syntax which is generally not observed with SQL syntax.

2. Creating Table Using Like Keyword From Another Table

SYNTAX:

PROC SQL;

CREATE TABLE tablename LIKE other_tablename;

QUIT;

SAS CODE:

proc sql;

insert into emp values (101, "Tommy Regan", 10, "10JUN1983"d);

insert into emp values (102, "Jin Andre", 11,"03JAN1970"d);

quit;

PROC SQL;

create table another_emp like emp;

describe table another_emp;

quit;

OUTPUT:

create table WORK.ANOTHER_EMP( bufsize=24576 )

(

empID num,

empname char(200),

deptID num,

dob num format=DATE9.

);

LIKE keyword creates a table just like another tables without the data. Means the newly created table will have only the metadata same as the original table but will not have any data records inside it though the original table might contain any number of records.

This can be confirmed with the following NOTE in the log.

NOTE: Table WORK.ANOTHER_EMP created, with 0 rows and 4 columns.

Though the original table emp has 2 records the newly created table has got only 0 records.

3. Creating Table Using AS Keyword From Another Table.

SYNTAX:

OUTPUT:

Considering the original emp table has the same status as above; output of this query can be observed as follows;

create table WORK.ANOTHER_EMP( bufsize=24576 )

(

empID num,

empname char(200),

deptID num,

dob num format=DATE9.

);

In addition to this it’s also worth to make a note of the following line in the log.

Unlike in the previous example we can see that the newly created table is clone of the original table with all the data records copied as it is.

Altering table structure using ALTER TABLE command with PROC SQL.

Altering Table Structure Using ALTER Table Command

SYNTAX:

The output in the log is self explanatory.

One can note that one column has been added each time when the add command is used; while drop command drops the column from the table.

And finally the table structure with SAL column added to the table.

Inserting Records(rows) Into Table Using INSERT Query

Inserting Rows Into table

There are multiple ways of using INSERT statement those are discussed below;

SYNTAX:

PROC SQL;

INSERT INTO tablename <(columnname_1 , columnname_2 , columnname_3... ......)> VALUES ( val_1, val_2, val_3,…………………);

QUIT;

SAS CODE:

OUTPUT:

SAS CODE:

OUTPUT:

Below is yet another way of inserting rows into table.

Records can also be inserted into one table directly from other table. Suppose there is another table called another_emp which looks like the following

Lets see, how one can insert these records from ‘another_emp’ into ‘emp’ .

SYNTAX:

PROC SQL;

INSERT INTO tablename

SELECT * FROM another_tablename;

QUIT;

SAS CODE:

OUTPUT:

One can observe the output that 2 more records with empID 105 and 106 have been added to ‘emp’ table with the same values as that of from the ‘another_emp’ table.

Updating Record Values of a Table Using UPDATE Query

Updating Record Values of a Table Using UPDATE Query

Using UPDATE query one can change the existing values of the columns in the table.

Basic syntax is as follows;

SYNTAX:

PROC SQL;

UPDATE tablename SET columnname = new_value;

QUIT;

However with this syntax new value will be assigned to each row of the table.

IF we want to change the value only for some selected columns we can use WHERE clause.

This has been shown with example below.

SAS CODE:

OUTPUT:

One can observe in the output that sal value has been updated to 3500 for empID 101 and 102.

Deleting Records From A Table Using DELETE Query

Deleting Records From A Table Using DELETE Query

Basic syntax is as follows;

SYNTAX:

PROC SQL;

DELETE * FROM tablename;

QUIT;

However with this syntax all the records from the table will get deleted.

IF one wants to delete only selected records then he/she can use WHERE clause.

This has been shown with example below.

SAS CODE:

OUTPUT:

The part of log has been given as output.

66

67 PROC SQL;

68 CREATE TABLE another_emp as SELECT * FROM emp;

NOTE: Table WORK.ANOTHER_EMP created, with 4 rows and 5 columns.

69 DROP TABLE another_emp;

NOTE: Table WORK.ANOTHER_EMP has been dropped.

70 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.15 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

Memory 7816k

OS Memory 15908k

Timestamp 4/30/2012 11:42:35 AM

Page Faults 0

Page Reclaims 20

Page Swaps 0

Voluntary Context Switches 20

Involuntary Context Switches 2

Block Input Operations 0

Block Output Operations 0

This is now all done with the basic SQL commands. Now we will move on to next topic XXXXXXXXXXXXX where we will discuss Types of Integrity Constraints, Indexes, SQL Joins and SET Operators