BookmarkSubscribeRSS Feed
Keith
Obsidian | Level 7

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;

3 REPLIES 3
Astounding
PROC Star

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

Hi Keith,

Does Tom's method work for you?

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

Thanks - Linlin

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 525 views
  • 0 likes
  • 3 in conversation