Proc Sql

1. Creating table Using Create Table Statement.

SYNTAX:

PROC SQL;

CREATE TABLE tablename (columnname_1 DATATYPE <FORMAT formatname> <LABELLabelname>, 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.

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.

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

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.

create table WORK.EMP( bufsize=24576 )

(

empID num,

empname char(200),

deptID num,

dob num format=DATE9.,

sal num

);

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 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:

columnname_2 = val_2,

columnname_3 = val_3;

QUIT;

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

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

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:

One can observe that; records corresponding to empID 105 and 106 has been deleted from the table.

Dropping The Table

Basic syntax is as follows;

SYNTAX:

PROC SQL;

DROP TABLE tablename;

QUIT;

There is difference between deleting all the records form the table using DELETE command and dropping the table using DROP command.

In later case the table with all its records and metadata will get deleted.

As we are going to use the ‘emp’ table for further demonstration; to demonstrate this scenario we will temporarily create another table with the name ‘another_emp’ and will then drop that table.

This has been shown 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