Prev: using ds2csv with char substitution
Next: HASH HELP NEEDED
From: MarySAS on 19 Apr 2010 15:06
I am working with two large datasets. The first table, JAN, is
huge, 140million records, distinct by PersonID, EventDate and Hour:

PersonID
EventDate
Hour
EventAmt


The second table, MASTER, has 2 fields RateCode and Cycle that I need
to lookup and put onto the first file. A person can have different
cycle and rate codes for different days or range of days. The Second
table has 1 million records, distinct by PersonID, FromDate and
ToDate:
PersonID
FromDate
ToDate
RateCode
Cycle

I’m sure you could do a SQL statement and get from table B values
where the ID’s match and the FromDate <= EventDate and ToDate >=
EventDate, but I think that a HASH would be much quicker. But, I’m
very new to HASH and am trying to figure out how to set this up.
Here’s what I have so far:

/
*****************************************************************************************/
Data JanNew;
Set Jan;

If _n_ = 0 then set Master;
If _n_ = 1 then do;
Declare hash MAS (Dataset : ‘master’, multidata: ‘Y’, Ordered: ‘A’)

Rc=mas.definekey (‘PersonId’);

Rc=mas.definedata (‘fromdt’, ‘todate’, ‘ratecode’, ‘cycle’);

RC=mas.definedone();

Do until (eof);
Set Jan end=eof;
RC=mas.find ();
If RC = 0 then do;
If FromDate <= EventDate <= ToDate then …………??????????
/
*****************************************************************************************/

I’m lost as to how to check the dates to make sure I am looking at the
right lookup records, then get the variables back from the hash object
and output the Event record.



 | 
Pages: 1
Prev: using ds2csv with char substitution
Next: HASH HELP NEEDED