WHERE= clause in DATA step

[hemi]

Hi,

I'm pulling my hair out at the moment with this problem. I've got two datasets around 10G each, both containing timestamped transactional records. I want to effectively merge the second into the first where DATE_PROCESSED is between DATE_START and DATE_APPLIED. Sounds simple enough... but when I use PROC SQL (using left join) it creates a huuuge temporary file and dies as there isn't enough workspace. We've got 65G free in the temporary directory which I should think is plenty for this task.

So I ended up giving up on that approach and tried using a nested SET statement in a data step:

data test (keep=CUST_NUMBER CUST_LOCATION TRANS_TYPE PERIOD_DATE
ELEMENT_SOURCE DATE_APPLIED DATE_CEASED DATE_START DATE_END
BALANCE INTEREST WOF);
set elements;

INTEREST = 0;
WOF = 0;
LastObs = 0;

do until(LastObs);
set interest_and_writeoffs
(where=(INT_CUST_NUMBER=CUST_NUMBER & INT_CUST_LOCATION=CUST_LOCATION
& PI_TRANS_TYPE=TRANS_TYPE & PI_PERIOD_DATE=PERIOD_DATE
& PI_DATE_PROCESSED >= DATE_START
& PI_DATE_PROCESSED <= dhms(datepart(DATE_APPLIED),0,0,0)));

if PI_CUST_NUMBER ne CUST_NUMBER or PI_CUST_LOCATION ne CUST_LOCATION
or PI_TRANS_TYPE ne TRANS_TYPE or PI_PERIOD_DATE ne PERIOD_DATE
or PI_DATE_PROCESSED > dhms(datepart(DATE_APPLIED),0,0,0)
then LastObs = 1;
else do;
INTEREST = INTEREST + PENALTIES_AND_INTEREST;
WOF = WOF + WRITEOFFS;
end;
end;

output;
run;


So basically the inner loop uses a WHERE= clause on the 'interest_and_writeoffs' dataset to get a subset matching the customer / period details in the main set statement. Except when I try and run it I get the error:

ERROR: Variable CUST_NUMBER is not on file INTEREST_AND_WRITEOFFS.

So I tried copying CUST_NUMBER to another variable first, but it still didn't like it. Upon consulting the manual, I see that the WHERE clause only takes constants, but WHERE= is supposed to be able to handle variables. Now i'm stumped and pretty close to doing nasty things to my workstation, and I think my colleagues are sick of hearing me curse, please help!

[sbb]

The SAS code from your post appears incomplete -- the WHERE= runs into some later code, I suspect, partially represented by the THEN.

You use of the SET with a WHERE= is in fact only applied to the process of loading the SAS PDV *FROM* the SAS file on the SET stmt. So, SAS is merely telling you that the variable is not on the file -- is that true? Should be easy to identify from a PROC CONTENTS.

Your idea of looping through the secondary file with each DATA step pass of the primary file is going to be influenced (from performance perspective) by the size of the secondary file.

First recommendation is to get yourself a "data sample" for each file to work with and validate the logic method processing first, before tackling the entire contents of both files.

Scott Barry
SBBWorks, Inc.

[hemi]

Thats weird, part of my code seems to be missing, but when I edit the message its actually there...

Ah well ignore the then part - there was an IF statement to check if the records were in bounds (I was using a WHERE instead of WHERE= before), which shouldn't be necessary if the code was working properly.

sbb - the variable is definately on file, but it seems to be complaining that both variables aren't from the same set. I tried using an arbritrary variable which I initialised before the do loop, but it still gave me the same error, i.e. it seems to be expecting both variables to be in the one dataset which seems to contradict what the manual says about the WHERE= clause.

[CurtisMack]

It is my understanding that the where clause is interpreted at compile time and therefore cannot reference values that are not in the dataset it modifies.

I would use the OPEN, FETCH, and CLOSE functions to accomplish this.

Curtis

[hemi]

Thanks Curtis - is there a way to seek to a particular record using the file i/o calls though? I see you can seek to a particular record by row number, but is there a way to utilise the index and seek to the first instance of a particular ID without having to traverse the entire table until the first instance is found?

[CurtisMack]

Yes,
The dataset name passed to the OPEN function can contain a WHERE clause. Build a string with the needed dataset reference with the where clause option, and pass that string to the OPEN function.

[hemi]

Thanks, i'll give this a try over the next couple of days.

In the end I managed to get the task done using brute force - I divided the data into 100 roughly equal sets and used PROC SQL on each subset, then joined all datasets back together. This still took around 20 hours though and if I can get your suggestion working I think it will run a lot quicker.

[ChrisNZ]

You'll need something like this:
[pre]

data OUT;
set SASHELP.CITIDAY(keep=DATE SNYDJCM);
DSID=open('SASHELP.CITIDAY(keep=DATE SNYSECM where=(DATE > "10jan1988"d and (DATE between ' ||
put(DATE-1,5.)||' and '||put(DATE+1,5.)||')))');
RC=fetch(DSID);
if RC=0 then
do while(RC=0);
SNYSECM=getvarn(DSID,varnum(DSID,'SNYSECM')); *replace varnum() by the number to speed up a wee bit;
output;
RC=fetch(DSID);
end;
else output;
RC=close(DSID);
run;
[/pre]
(I left join the table to itself within 1 day here).
Also, you'll need an index on the secondary table of course.

If you have version 9.2, you might want to look at hash tables as this version allows hash tables with non-unique keys.
2 dates + cusid + w/off + interest occupy 4+4+8+8+8=32 bytes. That's 33 million rows per GB of RAM.

Otherwise, the logic above with the secondary table loaded in memory using a sasfile statement is another option.

Message was edited by: Chris@NewZealand