Creating the Date Dimension Using SQL and SAS

Create Date. Surrogates Index CREATE TABLE Date. Surrogates (Order. Date CONSTRAINT pk PRIMARY KEY,

Create Date Key INSERT INTO Date. Surrogates (Order. Date) SELECT DISTINCT Order. Date FROM

Update Date. Key UPDATE Date. Surrogates SET Date. Key = Surrogate WHERE Date. Key

Date. Surrogates. Table Date. Surrogates: n n n Order. Date Surrogate Date. Key Date/Time

Importing Into SAS PROC IMPORT OUT= WORK. CUSTOMER DATATABLE= "Date. Surrogates“ DBMS=ACCESS 2000 REPLACE;

Results of Proc Contents ----Alphabetic List of Variables and Attributes----# Variable 3 Date. Key

SAS Date and Time Functions and Formats SAS has a number of useful functions

Functions take values and convert the way the data is stored Function Use Datapart

Functions Function Use Put(attribute, format) Returns a (character) value using a specified format. Returns

Formats and Informats Formats determine how a variable is displayed; informats determine how a

Some Useful Formats YEARw. MONNAMEw. DOWNAMEw. QTRw. JULDAYw. Writes date values as the year

Slides: 13

Download presentation

Creating the Date Dimension Using SQL and SAS to process dates.

Base E-R Diagram

Create Date. Surrogates Index CREATE TABLE Date. Surrogates (Order. Date CONSTRAINT pk PRIMARY KEY, Surrogate AUTOINCREMENT, Date. Key Integer);

Create Date Key INSERT INTO Date. Surrogates (Order. Date) SELECT DISTINCT Order. Date FROM Orders. Copy WHERE Order. Date NOT IN (SELECT Order. Date FROM Date. Surrogates);

Update Date. Key UPDATE Date. Surrogates SET Date. Key = Surrogate WHERE Date. Key IS Null;

Date. Surrogates. Table Date. Surrogates: n n n Order. Date Surrogate Date. Key Date/Time Autoincrement Integer

Importing Into SAS PROC IMPORT OUT= WORK. CUSTOMER DATATABLE= "Date. Surrogates“ DBMS=ACCESS 2000 REPLACE; DATABASE="C: Data. Warehousing 04 sET LPremiere. Extract. Example. mdb"; RUN;

Results of Proc Contents ----Alphabetic List of Variables and Attributes----# Variable 3 Date. Key 1 Order. Date 2 Surrogate Type Num Num Len 8 8 8 Pos Format 16 11. 0 DATETIME 20. 8 11. Informat 11. DATETIME 20. 11. Label Date. Key Order. Date Surrogate

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. n DATETIME stores both the date and time n DATE stores date information (in days from January 1, 1960) n TIME stores time information (in seconds. fraction)

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

Functions Function Use Put(attribute, format) Returns a (character) value using a specified format. Returns the value produced when a SAS expression is read using a specified informat. Input(attribute, informat)

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

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 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 w=3 the output is ddd.