Using SAS Data Step Options in Proc SQL

1
Using SAS Data Step Options in Proc SQL

• From laziness to innovation

Jill Casey Nova Scotia Department of Health
2
The Issue

• We have an analysis dataset with many sequential
  variables
• e.g. Dxcode1-Dxcode25
• We need to join a subset of the analysis dataset
  with an Oracle table to add region of residence.

3
Option 1

• proc sql
• create table SHRUG1 as
• select a. , b.Region
• from Table1 as a
• left join
• Table2 as b
• on a.IDb.ID
• quit

4
Option 2

• proc sql
• create table SHRUG1 as
• select a.ID, Dxcode1, Dxcode2,,Dxcode25,
  b.Region
• from Table1 as a
• left join
• Table2 as b
• on a.IDb.ID
• quit

5
Option 3

• In the DATA step, SAS allows for the use of
  variable list short-cuts
• data SHRUG1(keepID Dxcode1-Dxcode25)
• set

6
Option 3

• proc sql
• create table SHRUG1 as
• select a.ID, Dxcode1-Dxcode25, b.Region
• from Table1 as a
• left join
• Table2 as b
• on a.IDb.ID
• quit

7
Option 3

• proc sql
• create table SHRUG1 as
• select a.ID, Dxcode1-Dxcode25, b.Region
• from Table1 as a
• left join
• Table2 as b
• on a.IDb.ID
• quit

8
Theres got to be a way
9
Theres got to be a way

• The Essential PROC SQL Handbook

10
Theres got to be a way

• The Essential PROC SQL Handbook

11
Theres got to be a way

• The Essential PROC SQL Handbook
• Asked some SAS users

12
Theres got to be a way

• The Essential PROC SQL Handbook
• Asked some SAS users

13
Theres got to be a way

• The Essential PROC SQL Handbook
• Asked some SAS users
• Emailed SAS for help

14
Theres got to be a way

• The Essential PROC SQL Handbook
• Asked some SAS users
• Emailed SAS for help

15
Theres got to be a way

• The Essential PROC SQL Handbook
• Asked some SAS users
• Emailed SAS for help
• Searched the SAS Global Forum papers

16
Theres got to be a way

• The Essential PROC SQL Handbook
• Asked some SAS users
• Emailed SAS for help
• Searched the SAS Global Forum papers

17
NESUG 2006

• Variable List Short-Cuts in
• Proc SQL
• (Borowiak, K, et al.)

18
Option 4

• proc sql
• create table SHRUG1 as
• select a., b.Region
• from Table1(keepID Dxcode1-Dxcode25) as a
• left join
• Table2 as b
• on a.IDb.ID
• quit

19
Option 4

• proc sql
• create table SHRUG1(keepID Dxcode1-Dxcode25
  Region)
• as
• select a., b.Region
• from Table1 as a
• left join
• Table2 as b
• on a.IDb.ID
• quit

20
Questions??