1 / 13

Creating the Date Dimension

Creating the Date Dimension. Using SQL and SAS to process dates. Base E-R Diagram. Create DateSurrogates Index. CREATE TABLE DateSurrogates (OrderDate Date CONSTRAINT pk PRIMARY KEY, Surrogate AUTOINCREMENT, DateKey Integer);. Create Date Key. INSERT INTO DateSurrogates

tab
Download Presentation

Creating the Date Dimension

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 PROCIMPORT OUT= WORK.CUSTOMER DATATABLE= "DateSurrogates“ DBMS=ACCESS2000 REPLACE; DATABASE="C:\DataWarehousing04s\ETL\PremiereExtractExample.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. 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. Functions

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

More Related