Summary

Text Summary

Understanding Methods of Creating Tables

You can use the CREATE TABLE statement to create a table in three different ways:
  • create a table with no rows (an empty table) by defining columns
  • create an empty table that is like another table
  • create a table that contains rows, based on a query result.

Creating an Empty Table By Defining Columns

You can create a table with no rows by using a CREATE TABLE statement that contains column specifications. A column specification includes the following elements: column name (required), data type (required), column width (optional), one or more column modifiers (optional), and a column constraint (optional).

Displaying the Structure of a Table

To display, in the SAS log, a list of a table's columns and their attributes and other information about a table, use the DESCRIBE TABLE statement.

Creating an Empty Table That Is like Another Table

To create a table with no rows that has the same structure as an existing table, use a CREATE TABLE statement that contains the keyword LIKE. To specify a subset of columns to be copied from the existing table, use the SAS data set options DROP= or KEEP= in your CREATE TABLE statement.

Creating a Table from a Query Result

To create a new table that contains both columns and rows that are derived from an existing table or set of tables, use a CREATE TABLE statement that includes the keyword AS and the clauses that are used in a query. This method enables you to copy an existing table quickly.

Inserting Rows of Data into a Table

The INSERT statement can be used in three ways to insert rows of data in existing tables, either empty or populated. You can insert rows by using
  • the SET clause to specify column names and values in pairs
  • the VALUES clause to specify a list of values
  • the clauses that are used in a query to return rows from an existing table.

Creating a Table That Has Integrity Constraints

Integrity constraints are rules that you can specify in order to restrict the data values that can be stored for a column in a table. To create a table that has integrity constraints, use a CREATE TABLE statement. Integrity constraints can be defined in two different ways in the CREATE TABLE statement:
  • by specifying a column constraint in a column specification
  • by using a constraint specification.

Handling Errors in Row Insertions

When you add rows to a table that has integrity constraints, PROC SQL evaluates the new data to ensure that it meets the conditions that are determined by the integrity constraints. When you use the INSERT or UPDATE statement to add or modify data in a table, you can use the UNDO_POLICY= option in the PROC SQL statement to specify whether PROC SQL will make or undo the changes that you submitted up to the point of the error.

Displaying Integrity Constraints for a Table

To display the integrity constraints for a specified table in the SAS log, use the DESCRIBE TABLE CONSTRAINTS statement.

Updating Values in Existing Table Rows

To modify data values in some or all of the existing rows in a table, use the UPDATE statement with the following:
  • a SET clause and possibly a WHERE clause that specifies a single expression to update rows. To update rows with multiple expressions, use multiple UPDATE statements.
  • a CASE expression that specifies multiple expressions to update rows. The CASE expression can be specified without an optional case operand or, if the expression in the SET clause uses an equals (=) comparison operator, with a case operand.
The CASE expression can also be used in the SELECT statement in a new column definition to specify different values for different subsets of rows.

Deleting Rows in a Table

To delete some or all of the rows in a table, use the DELETE statement.

Altering Columns in a Table

To alter columns in a table, use the ALTER TABLE statement that contains one or more of the following clauses:
  • the ADD clause, to add one or more columns to a table
  • the DROP clause, to drop (delete) one or more columns in a table
  • the MODIFY clause, to modify the attributes of columns in a table.

Dropping Tables

To drop (delete) one or more entire tables, use the DROP TABLE statement.

Sample Programs

Creating an Empty Table By Defining Columns

 proc sql;
    create table work.discount 
           (Destination char(3),
           BeginDate num Format=date9.,
           EndDate num format=date9.,
           Discount num);
quit;

Creating an Empty Table That Is like Another Table

proc sql;
   create table work.flightdelays2
          (drop=delaycategory destinationtype) 
      like sasuser.flightdelays;
quit;

Creating a Table from a Query Result

proc sql;
   create table work.ticketagents as
      select lastname, firstname,
             jobcode, salary
         from sasuser.payrollmaster,
              sasuser.staffmaster
         where payrollmaster.empid
               = staffmaster.empid
               and jobcode contains 'TA';
quit;

Displaying the Structure of a Table

proc sql;
   describe table work.discount;
quit;

Inserting Rows into a Table By Specifying Column Names and Values 

proc sql;
   insert into work.discount
        set destination='LHR',
            begindate='01MAR2000'd,
            enddate='05MAR2000'd,
            discount=.33
        set destination='CPH',
            begindate='03MAR2000'd,
            enddate='10MAR2000'd,
            discount=.15;
quit;

Inserting Rows into a Table By Specifying Lists of Values

proc sql;
   insert into work.discount (destination, 
          begindate,enddate, discount)
      values ('LHR','01MAR2000'd, 
             '05MAR2000'd,.33)
      values ('CPH','03MAR2000'd,
             '10MAR2000'd,.15);
quit;

Inserting Rows into a Table from a Query Result

proc sql; 
   insert into work.payrollchanges2
      select empid, salary, dateofhire
         from sasuser.payrollmaster
         where empid in ('1919','1350','1401'),
quit;

Creating a Table That Has Integrity Constraints

proc sql;
   create table work.employees
          (Name char(10),
          Gender char(1),
          HDate date label='Hire Date' not null,
          constraint prim_key primary key(name),
          constraint gender check(gender in ('M' 'F')));
quit;

Displaying Integrity Constraints for a Table

proc sql;
   describe table constraints work.discount4;
quit;

Updating Rows in a Table Based on an Expression

proc sql;
   update work.payrollmaster_new
      set salary=salary*1.05
      where jobcode like '__1';
quit;

Updating Rows in a Table By Using a CASE Expression

proc sql;
   update work.payrollmaster_new 
      set salary=salary*
         case 
            when substr(jobcode,3,1)='1'
                 then 1.05
            when substr(jobcode,3,1)='2'
                 then 1.10
            when substr(jobcode,3,1)='3'
                 then 1.15
            else 1.08
         end;
quit;

Updating Rows in a Table By Using a CASE Expression (Alternate Syntax) 

proc sql outobs=10;
   select lastname, firstname, jobcode, 
          case substr(jobcode,3,1)
               when '1'
                    then 'junior'
               when '2'
                    then 'intermediate'
               when '3'
                    then 'senior'
               else 'none'
          end as JobLevel
      from sasuser.payrollmaster,
           sasuser.staffmaster
      where staffmaster.empid=
            payrollmaster.empid;
quit;

Deleting Rows in a Table

proc sql;
   delete from work.frequentflyers2
      where pointsearned-pointsused<=0;
quit;

Adding, Modifying, and Dropping Columns in a Table

proc sql;
   alter table work.payrollmaster4
      add Age num 
      modify dateofhire date format=mmddyy10.
      drop dateofbirth, gender;
quit;

Dropping a Table

proc sql;
   drop table work.payrollmaster4;
quit;

Points to Remember

  • The CREATE TABLE statement generates only a table as output, not a report.
  • The UPDATE statement does not insert new rows into a table. To insert rows, you must use the INSERT statement.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.8.153