07-25-2012 09:46 AM
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.
input id log_date :date9. role;
format log_date date9.;
1 25jul2012 2
2 25jul2012 2
3 25jul2012 4
4 25jul2012 1
5 25jul2012 1
6 25jul2012 1
input id (date_from date_to) (:date9.) group role;
format date: date9.;
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
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);
07-25-2012 11:18 AM
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:
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.) || '_';
if done then do;
proc sort data=lookup2;
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;
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:
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;
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.