Creating the Date Dimension

1
Creating the Date Dimension

• Using SQL and SAS to process dates.

2
Base E-R Diagram
3
Create DateSurrogates Index

• CREATE TABLE DateSurrogates
• (OrderDate Date
• CONSTRAINT pk PRIMARY KEY,
• Surrogate AUTOINCREMENT,
• DateKey Integer)

4
Create Date Key

• INSERT INTO DateSurrogates
• (OrderDate)
• SELECT DISTINCT OrderDate
• FROM OrdersCopy
• WHERE OrderDate
• NOT IN (SELECT OrderDate FROM
  DateSurrogates)

5
Update DateKey

• UPDATE DateSurrogates SET DateKey Surrogate
• WHERE DateKey IS Null

6
DateSurrogatesTable

• DateSurrogates
• OrderDate Date/Time
• Surrogate Autoincrement
• DateKey Integer

7
Importing Into SAS

• PROC IMPORT
• OUT WORK.CUSTOMER
• DATATABLE "DateSurrogates
• DBMSACCESS2000 REPLACE
• DATABASE"C\DataWarehousing04s\ETL\PremiereExtrac
  tExample.mdb"
• RUN

8
Results of Proc Contents

• ----Alphabetic List of Variables and
  Attributes-----
• Variable Type Len Pos Format
  Informat Label
• 3 DateKey Num 8 16 11.
  11. DateKey
• 1 OrderDate Num 8 0
  DATETIME20. DATETIME20. OrderDate
• 2 Surrogate Num 8 8 11.
  11. Surrogate

9
SAS Date and Time Functions and Formats

• SAS has a number of useful functions for working
  with dates and times. Their options depend on
  the data type of the variable.
• DATETIME stores both the date and time
• DATE stores date information (in days from
  January 1, 1960)
• TIME stores time information (in seconds.fraction)

10
Functions

• Functions take values and convert the way the
  data is stored
• Function Use
• Datapart Takes a datetime attribute, extracts
  the date part and stores it as a date
  attribute
• Timepart Takes a datetime attribute, extracts the
  time part and stores it as a Time attribute

11
Functions

• Function Use
• Put(attribute, format)
• Input(attribute, informat)

• Use
• Returns a (character) value using a specified
  format.
• Returns the value produced when a SAS expression
  is read using a specified informat.

12
Formats and Informats

• Formats determine how a variable is displayed
  informats determine how a variable id read. The
  tend to be similar.
• FORMATNAMEw.d
• Formats and Informats always contain a period (.)
• SAS makes assumptions about the best display for
  a given w.d specification

13
Some Useful Formats

• YEARw.
• MONNAMEw.
• DOWNAMEw.
• QTRw.
• JULDAYw.

• Writes date values as the year
• Writes date values as the quarter of the year
• Writes data values as the the name of the day of
  the week
• Writes date values as the quarter of the year
• writes dates as the Julian day of the year
  (yyddd). For w3 the output is ddd.