CSULB_STAT475_handout25

CSULB_STAT475_handout25 - STAT 475 Chapter 5 CREATING AND...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
STAT 475 Chapter 5 CREATING AND MANAGING TABLES USING PROC SQL Reference SAS Certification Prep Guide: Advanced Programming for SAS 9 There are three methods to create a new table with PROC SQL: Creating a table from a query result Creating an empty table that has the same columns as another table Creating an empty table by defining columns CREATING A TABLE FROM A QUERY RESULT A new table may be created and populated with data from the result of a PROC SQL query. The syntax is proc sql; create table table_name as select column1< , column2 , …> from table1< , table2 , …> <optional query clauses> quit; Example. The following query computes the inner join of tables one and two , creates a table named inner_join in the Work library, and prints the contents of this table. data one; input id varA $ @@; datalines; 1 a 2 b 4 d ; data two; input id varB $ @@; datalines; 2 x 3 y 5 v ; 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
options nodate nonumber; title; proc sql; create table inner_join as select * from one, two where one.id=two.id; select * from inner_join; quit; The output is id varA varB ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 2 b x CREATING AN EMPTY TABLE THAT IS LIKE ANOTHER TABLE To create a new table that has the same columns as an existing table but has no rows, use a CREATE TABLE statement with a LIKE clause. The syntax is proc sql; create table table_name like table1 ; quit; Example. In the previous example, a new table named new is created that has the same columns as inner_join table but has no data. proc sql; create table new like inner_join; quit; Note that the table new cannot be printed as it doesn’t contain any data, but its contents can be viewed by clicking on the file in the Work library. 2
Background image of page 2
To create a new table that contains only a specified subset of columns from an existing table, use DROP or KEEP options. The syntax is proc sql; create table table_name (drop = column1 < column2 , …>) /* or (keep = column1 < column2 , …>) */ like table1 ; quit; Example. In the previous example, a new table named new is created that has only columns varA and varB and has no data. proc sql;
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/14/2011 for the course STAT 475 taught by Professor Olgak during the Fall '11 term at CSU Long Beach.

Page1 / 11

CSULB_STAT475_handout25 - STAT 475 Chapter 5 CREATING AND...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online