Data Management Solutions Using SAS Hash Table Operations: A Business Intelligence Case Study by Paul Dorfman & Don Henderson

Author:Paul Dorfman & Don Henderson [Dorfman, Paul]
Language: eng
Format: epub
Publisher: SAS Institute
Published: 2018-07-08T23:00:00+00:00

7.3.6.1 Performing Table Lookups Using an SCD Type 6 Table

The table lookup into an SCD Type 6 table can be done using the exact same code as shown above for an SCD Type 2 table. However, that does not leverage the potential efficiency benefits of using the Active flag. The following code illustrates first checking to see if the Active row meets the required time/date constraint; and if it does not, the inactive rows are searched.

Program 7.6 Chapter 7 SCD 6.sas (continued)

data tableLookup;

/* Sample Lookup */

retain Player_ID;

if 0 then set bizarro.Players_SCD6(drop=Subkey);

if _n_ = 1 then

do; /* define the hash table */

dcl hash scd(dataset:"bizarro.Players_SCD6",multidata:"Y",ordered:"D"); ❶

scd.defineKey("Player_ID","Active");

scd.defineData("Team_SK","Player_ID","Active","First_Name","Last_Name"

,"Position_Code","Bats","Throws","Start_Date","End_Date");

scd.defineDone();

end; /* define the hash table */

infile datalines;

attrib Date format = yymmdd10. informat = yymmdd10.;

input Player_ID Date; ❷

RC = scd.find(Key:Player_ID,Key:1); ❸

if RC = 0 and (Start_Date le Date le End_Date)

then; ❹

else

do; /* search the inactive rows */

RC = scd.find(Key:Player_ID,Key:0); ❺

do while(RC = 0);

if (Start_Date le Date le End_Date) then leave;

RC = scd.find_next();

end;

end; /* search the inactive rows */

if RC ne 0 then call missing(Team_SK,Active,First_Name,Last_Name ❻

,Position_Code,Bats,Throws,Start_Date,End_Date);

datalines;

10103 2017/10/15

10103 2017/03/23

99999 2017/03/15

10782 2017/03/22

10782 2017/03/21

run;

❶ Define the hash table. Note that adding the Active field as a key can improve lookup performance, especially if the majority of lookups are for the active values.

❷ Read in the sample transactions for our lookups (this is the same data as used for the SCD Type 2 lookup example).

❸ We first use the FIND method to Retrieve the values for the currently active row.

❹ If there is a currently active item (RC = 0), and Date is in range, our search is complete.

❺ Otherwise, search for the inactive items.

❻ If the search fails because either the Player_ID key-value is not found in the hash table or there is no date range match, set the values of the dimensional variables to missing.

The results of the lookup can be seen in the following output.

Output 7.7 Sample SCD Type 6 Lookup Results

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.