Creating an Empty Table That Is like Another Table

Overview

Sometimes you want to create a new table that has the same columns and attributes as an existing table, but has no rows. To create an empty table that is like another table, use a CREATE TABLE statement with a LIKE clause.
General form, CREATE TABLE statement with a LIKE clause:
CREATE TABLE table-name
LIKE table-1;
Here is an explanation of the syntax:
table-name
specifies the name of the table to be created.
table-1
specifies the table whose columns and attributes are copied to the new table.

Example

Suppose you want to create a new table, Work.Flightdelays2, that contains data about flight delays. You would like the new table to contain the same columns and attributes as the existing table Sasuser.Flightdelays, but you do not want to include any of the existing data. The following PROC SQL step uses a CREATE TABLE statement and a LIKE clause to create Work.Flightdelays2: 
proc sql;
   create table work.flightdelays2 
      like sasuser.flightdelays;
The following DESCRIBE TABLE statement displays the structure of the empty table Work.Flightdelays2: 
proc sql;
   describe table work.flightdelays2;
Table 5.8 SAS Log 
NOTE: SQL table WORK.FLIGHTDELAYS2 was created like:
		
create table WORK.FLIGHTDELAYS2( bufsize=8192 )
  (
   Date num format=DATE9. informat=DATE9.,
   FlightNumber char(3),
   Origin char(3),
   Destination char(3),
   DelayCategory char(15),
   DestinationType char(15),
   DayOfWeek num,
   Delay num
  );
Work.Flightdelays2 contains eight columns, as listed.

Specifying a Subset of Columns from a Table

If you want to create an empty table that contains only a specified subset of columns from an existing table, use the SAS data set options DROP= or KEEP=.
General form, DROP=, and KEEP= data set options:
(DROP | KEEP =column-1< ...column-n>)
Here is an explanation of the syntax:
column
specifies the name of a column to be dropped or kept. Multiple column names must be separated by spaces. The entire option must be enclosed in parentheses.
In the CREATE TABLE statement, the DROP= or KEEP= option can be inserted in either of the following locations:
  • between the name of the table that is being created and the LIKE clause (as shown in the following example)
  • after the name of the source table, at the end of the LIKE clause.

Example

The following PROC SQL step creates the new table Work.Flightdelays3 that contains a subset of columns from the table Sasuser.Flightdelays. The DROP= option is used to specify that all columns except DelayCategory and DestinationType are included in the new table. 
proc sql;
   create table work.flightdelays3
          (drop=delaycategory destinationtype)
      like sasuser.flightdelays;
For comparison, the results of running the DESCRIBE TABLE statement for the original table and the new table are shown below.
Table 5.9 SAS Log 
NOTE: SQL table WORK.FLIGHTDELAYS was created like:
		
create table SASUSER.FLIGHTDELAYS( bufsize=8192 )
  (
   Date num format=DATE9. informat=DATE9.,
   FlightNumber char(3),
   Origin char(3),
   Destination char(3),
   DelayCategory char(15),
   DestinationType char(15),
   DayOfWeek num,
   Delay num
  );
Table 5.10 SAS Log 
NOTE: SQL table WORK.FLIGHTDELAYS was created like:
		
create table WORK.FLIGHTDELAYS3( bufsize=4096 )
  (
   Date num format=DATE9. informat=DATE9.,
   FlightNumber char(3),
   Origin char(3),
   Destination char(3),
   DayOfWeek num,
   Delay num
  );
As these messages show, Sasuser.Flightdelays contains the columns DelayCategory and DestinationType. Work.Flightdelays3 does not contain the columns.
Note: In PROC SQL, you can apply most of the SAS data set options, such as DROP= and KEEP=, to tables anytime that you specify a table. You can use a more limited set of SAS data set options with PROC SQL views. However, because the DROP= and KEEP= options are SAS options and not part of the ANSI standard for SQL, you can use the DROP= and KEEP= options only with the SAS implementation of SQL.
..................Content has been hidden....................

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