Variable List Short-Cuts in PROC SQL

1
Variable List Short-Cuts in PROC SQL

• Ken Borowiak
• Scannell Kurz, Inc.
• Rochester, NY

2
SCORES Data Set

Numeric
Character
3
SELECT Short-Cut

• proc sql
• create table Scores1 as
• select T1.
• from Scores as T1
• where visit1
• quit

4
SELECT Short-Cut

• proc sql
• create table Scores1 as
• select T1.
• from Scores as T1
• where visit1
• quit
• The short-cut in the SELECT statement means
  keep all uniquely named fields

5
SELECT ALL BUT A FEW FIELDS

• Type in a long list of fields excluding the
  unwanted fields
• Use SELECT and live with the excess baggage

6
SELECT EXCEPT

• Such a construct would be handy
• Keep wishing it does not exist
• There is still hope!!!

7
SELECT ALL FIELDS EXCEPT A1 and A3

• proc sql
• create table Scores1 as
• select
• from Scores(dropA1 A3)
• quit

DROP Data Set Option
8
Exclude All B Fields

• proc sql
• create table no_Bs_1 as
• select
• from Scores(keepSubject_id Visit
  A1-A10)
• quit

Numbered range list
9
Exclude All B Fields

• proc sql
• create table no_Bs_2 as
• select
• from Scores(dropB1--B10)
• quit

Name Range List
10
Exclude All B Fields

• proc sql
• create table no_Bs_3 as
• select
• from Scores(dropB)
• quit

Name Prefix List
11
Exclude All B Fields

• proc sql
• create table no_Bs_4 as
• select
• from Scores(keepSu _numeric_)
• quit

Variable class list
12
Important Point

• The variable list short-cuts referenced in the
  FROM clause via the DROP and KEEP data set
  options ARE NOT valid in the SELECT statement

13
DROP Data Set Option in the CREATE TABLE
Statement

• proc sql
• create table Scores1(dropB) as
• select ,
• sum(input(B1,8.3),
  input(B2,8.3)) as S_B1_B2
• from Scores(dropB3--B10)
• where visit between 1 and 10
• quit

14
FROM Clause Data Set Option Limitations

• DICTIONARY Tables NO!
• SASHELP Views NO!
• DATA Steps Views YES!
• SQL Views NO!
• Pass-Through Facility NO!
• RDBMS Libref YES!

15
Questions?

•  

• SHAMELESS POSTER PLUG
• Perl Regular Expressions 102
• Today 1030 1130

16
Contact Info

• Ken Borowiak
• EvilPettingZoo97_at_aol.com
• SAS and all other SAS Institute Inc. product or
  service names are registered trademarks blah
  blah blah