Proc Sql optimisation - Hash?

[Keith]

Hi all,

I have 2 datasets, a master and a lookup.  The master has id, log_date and role.  I want to update the role column in master from the lookup dataset which has id, date_from, date_to, group and role.  One id can belong to more than one group at the same time, however I am only interested in group 1, so the data is unique when dates are taken into account.  I've written a Proc Sql statement to update master, but this is taking hours to run (the master dataset has around 15 million rows, the lookup dataset around 17 thousand).  I assume this is due to the where clause I've had to include.  Does anyone know of a more efficient way to update for this type of query?  Possibly using Hash?  Sadly I'm still in the dark ages of 9.1.3, so I'm not able to use the extra Hash functionality that came in 9.2 (fuzzy matching).

Below is an example of the 2 datasets I have, along with the Proc Sql code I'm currently running.

data master;

input id log_date :date9. role;

format log_date date9.;

cards;

1 25jul2012 2

2 25jul2012 2

3 25jul2012 4

4 25jul2012 1

5 25jul2012 1

6 25jul2012 1

;

run;

data lookup;

input id (date_from date_to) (:date9.) group role;

format date: date9.;

cards;

1 01jul2012 01aug2012 1 10

1 01jul2012 01aug2012 2 20

2 01jul2012 01aug2012 1 10

2 01jul2012 01aug2012 2 30

3 01jun2012 30jun2012 1 10

3 01jul2012 01aug2012 1 20

3 01jul2012 01aug2012 2 40

4 01jul2012 01aug2012 1 20

5 01jul2012 01aug2012 2 20

;

run;

proc sql;

update master as a

  set role=(select role from lookup as b

                 where a.id=b.id and a.log_date between b.date_from and b.date_to and b.group=1)                   

  where exists (select id from lookup as c

                      where a.id=c.id and a.log_date between c.date_from and c.date_to and c.group=1);    

quit;

[Astounding]

Keith,

Here's the old-fashioned way, before hash tables existed.

Use the smaller file to create two formats (one for each GROUP) that translate from ID + date into ROLE.  For the first line of your data, it would be equivalent to:

'0120120701' - '0120120801' = '10'

Note how putting dates in a YYMMDD format creates a valid range.

Here is one way to approach that step:

data lookup2;

set lookup end=done;

start = put(id,z2.) || put(date_from,yymmddn8.);

end = put(id,z2.) || put(date_to, yymmddn8.);

fmtname = '$findgroup' || put(group,1.) || '_';

label=role;

output;

if done then do;

   hlo='O';

   label='Not found';

   fmtname='$findgroup1_';

   output;

   fmtname='$findgroup2_';

end;

run;

proc sort data=lookup2;

   by fmtname;

run;

You might tweak that code in a few ways, adjusting the formats used or switching to CAT functions.  But once you get that output, create the formats using:

proc format cntlin=lookup2;

run;

Then a DATA step can easily retrieve the pieces and you can apply whatever logic you would like.  I leave the logic to you but here is the lookup portion:

data master;

   set master;

   lookup_string = put(id,z2.) || put(log_date,yymmddn8.);

   match_on_group1 = put(lookup_string, $findgroup1_.);

   match_on_group2 = put(lookup_string, $findgroup2_.);

   * then add your logic to utilize the matching strings;

run;

Depending on the logic you want to apply, you may or may not need the second format.  As an easy final step, remember to drop any extra variables.  I would be interested to know how much improvement you see in the speed.

Good luck.

[Keith]

Ah yes, I'm familiar with merging using formats but I hadn't thought of using it here.  I'll see if improves efficiency significantly.

[Linlin]

Hi Keith,

Does Tom's method work for you?

https://communities.sas.com/thread/34025

Thanks - Linlin