topic Re: Why my Temp Array unduplication utterly fails vs HASH in SAS Programming

Why my Temp Array unduplication utterly fails vs HASH

novinosrin — Sun, 03 Nov 2019 22:51:17 GMT

Why my Temp Array unduplication utterly fails vs HASH?

Objective : To get unique account_numbers

Source data: 33 Million records



/*Runs forever, not completing at all :( */
data w;
 do until(z);
  set aqr.rp_portfolio_&min-aqr.rp_portfolio_&max 
  (keep=account_number portfolio cra_flag period id_investor)
  end=z;
  where period>= 1439 and id_investor not in ('014', '015', '091', '092') or
  period<1439;
  array t(9999999) _temporary_;
  length port_name $30;
  if account_number in t then continue;
  select;
   when (portfolio='Mortgage') port_name='Treasury';
   when (portfolio='Resi' and cra_flag='Y') port_name='CRA';
   when (portfolio in('Resi','HELOAN Low/No') and cra_flag='N') port_name='Resi (No CRA)';
   when (portfolio='Specialty') port_name=portfolio;
   otherwise port_name=' ';
  end;
  if port_name>' ' then do;
   n+1;
   t(n)=account_number;
   output;
  end;
 end;
 stop;
 keep account_number port_name;
run;

What's wrong with me or my eyes that isn't spotting something very obvious in the previous, whereas the HASH equivalent below runs in real time 6:49.53
user cpu time 1:26.36
system cpu time 24.94 seconds
memory 33221.57k
OS Memory 58464.00k

data w;
 dcl hash H () ;
 h.definekey  ("account_number") ;
 h.definedata ("account_number") ;
 h.definedone () ;
 do until(z);
  set aqr.rp_portfolio_&min-aqr.rp_portfolio_&max 
  (keep=account_number portfolio cra_flag period id_investor)
  end=z;
  where period>= 1439 and id_investor not in ('014', '015', '091', '092') or
  period<1439;
  length port_name $30;
  if h.check()=0 then continue;
  select;
   when (portfolio='Mortgage') port_name='Treasury';
   when (portfolio='Resi' and cra_flag='Y') port_name='CRA';
   when (portfolio in('Resi','HELOAN Low/No') and cra_flag='N') port_name='Resi (No CRA)';
   when (portfolio='Specialty') port_name=portfolio;
   otherwise port_name=' ';
  end;
  if port_name>' ' then do;
   h.add();
   output;
  end;
 end;
 stop;
 keep account_number port_name;
run;

Any thoughts?

Re: Why my Temp Array unduplication utterly fails vs HASH

Kurt_Bremser — Sun, 03 Nov 2019 22:55:50 GMT

You could have more than 10 million unique accounts.

Also, the search in the array is sequential, while the hash find uses a b-tree or something similar performant.

Re: Why my Temp Array unduplication utterly fails vs HASH

novinosrin — Sun, 03 Nov 2019 23:18:10 GMT

Sir "You could have more than 10 million unique accounts" -This is funny though, we wish we were that big to have a huge portfloio. However, for a regional bank, our portfolio size isn't too bad either. 🙂 Nonetheless, that did make me think more. I am taking 82 months data min-max(range) as you would have noticed in the SET statement. The HASH successful output has given me 190,000 which is right.

Hmm, so am i to believe and learn ARRAY IN operator search is useless?? Oh gosh!

Re: Why my Temp Array unduplication utterly fails vs HASH

mkeintz — Sun, 03 Nov 2019 23:27:32 GMT

@novinosrin

Are your account number integers in the range {-constant('bigint'):constant('bigint')? Probably not, since I think you would likely use direct array lookup in such a case.

But are the account numbers mappable in a 1 to 1 correspondence to such a range? If so, then instead of searching an array you could do direct lookup of the transformed account number.

Could you tell us the structure of your account numbers?

Re: Why my Temp Array unduplication utterly fails vs HASH

novinosrin — Sun, 03 Nov 2019 23:33:53 GMT

Looks something like this

1829647
7329386
7339930
9515503
10005882
10039832
10053965
10065076
10068369
10073393

5777

70396902245

And all are stored as numeric.

Re: Why my Temp Array unduplication utterly fails vs HASH

Astounding — Mon, 04 Nov 2019 00:45:32 GMT

One item to note:

The (keep= ) list applies to the last data set only. For all the other source data sets, the program reads in all variables. At least that's what it looks like.

Re: Why my Temp Array unduplication utterly fails vs HASH

novinosrin — Mon, 04 Nov 2019 00:56:34 GMT

Sir @Astounding You nearly gave me a heart attack . Luckily I checked the doc again before I would have jumped out of the building which the ARRAY IN is making me contemplate that. 🙂

(data-set-options)

specifies actions SAS is to take when it reads variables or observations into the program data vector for processing.

https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=p00hxg3x8lwivcn1f0e9axziw57y.htm&docsetVersion=9.4&locale=en

Tip	Data set options that apply to a data set list apply to all of the data sets in the list.
See	For more information, see Definition of Data Set Options in SAS Data Set Options: Reference for a list of the data set options to use with input data sets.

Re: Why my Temp Array unduplication utterly fails vs HASH

Astounding — Mon, 04 Nov 2019 01:08:33 GMT

OK, sorry about that. The last time I saw that part of the documentation, data set lists didn't exist. I promise to take a second look and see what I can find.

Re: Why my Temp Array unduplication utterly fails vs HASH

PGStats — Mon, 04 Nov 2019 01:44:54 GMT

I guess your tables are not sorted or indexed by account number?

Re: Why my Temp Array unduplication utterly fails vs HASH

novinosrin — Mon, 04 Nov 2019 01:58:34 GMT

Sir @PGStats True. This is for that very purpose indeed. The simple HAVE and WANT below demo works exactly as intended, but when reading company data, it isn't. 😞

For example:


data have;
 input acctno;
 cards;
 8
 3
 8
 3
 10
 3
 6
 5
 3
 ;

data want;
 do until(z);
  set have end=z;
  array t(9) _temporary_;
  if acctno in t then continue;
  n+1;
  t(n)=acctno;
  output;
 end;
 drop n;
run;

proc print noobs;run;

acctno
8
3
10
6
5

Re: Why my Temp Array unduplication utterly fails vs HASH

ChrisNZ — Mon, 04 Nov 2019 02:10:09 GMT

> am i to believe and learn ARRAY IN operator search is useless?

As @Kurt_Bremser said, "the search in the array is sequential, while the hash find uses a b-tree".

For each new account (there are 200,000), you are sequentially searching through 10 million array values.

That's 2e5 * 1e7 = 2e12 searches. Two thousand billion searches. 2,000,000,000,000 sequential searches.

Plus other searches for the other 33,000,000 records where a key is found earlier without scanning the whole array.

How do you expect this to be fast?

And even if magnitude wasn't an issue (it is), how could a sequential search be compared to an indexed hash search?

So useless? Absolutely not. The in operator for an array has its place. But tools are only good when used within their specifications. A F1 car is useless off-road and a buggy is useless on the tarmac. Likewise, expecting to search an array the way you are trying can only fail.

Re: Why my Temp Array unduplication utterly fails vs HASH

novinosrin — Mon, 04 Nov 2019 02:18:45 GMT

That's very disappointing to know it's sequential.

Does that mean WHICHC,WHICHN all will also be ~~useless~~ too? Do these also do follow suit (i.e sequential search)?

Re: Why my Temp Array unduplication utterly fails vs HASH

ChrisNZ — Mon, 04 Nov 2019 02:41:12 GMT

@novinosrin Don't blame the tool. All tools have specifications and limitations. The key is to know these and use the tools in an optimal manner.

You can also suggest improvements, but that's an other matter (and doesn't preclude knowing the specifications and limitations).

Re: Why my Temp Array unduplication utterly fails vs HASH

ChrisNZ — Mon, 04 Nov 2019 22:51:48 GMT

Which begs the question: Why create a 10-million-values array to store 200,000 values?

Re: Why my Temp Array unduplication utterly fails vs HASH

novinosrin — Mon, 04 Nov 2019 02:49:01 GMT

Agree.

Re: Why my Temp Array unduplication utterly fails vs HASH

Astounding — Mon, 04 Nov 2019 03:33:22 GMT

OK, here's an approach that might be feasible, might not. The issues .....

It treats account number as numeric. While you can easily work around that, the cost for that is not clear.

It utilizes the direct array lookup that @mkeintz mentioned.

It needs a lot more memory. Whether you can get that much is not clear ... on the order of 400 times what you are using now for the temporary array. The array expands to 11 digits, since the largest account number you illustrated is 11 digits long. The memory requirements might be shrinkable, since it appears that you don't really need a length of $30 for PORT_NAME.

I took out the DO loop, since I didn't see a good purpose for keeping it. Perhaps its purpose is to maintain a top-to-bottom structure for the DATA step logic.

If all this is acceptable, it should be lightning fast at loading the values, and should take a bit more time to unload them.


data w;
  array t {99999999999} $ 30 _temporary_;
  if z then do account_number = 1 to 99999999999;
     if t{account_number} > ' ' then do;
        port_name = t{account_number};
        output;
     end;
  end;   
  set aqr.rp_portfolio_&min-aqr.rp_portfolio_&max 
  (keep=account_number portfolio cra_flag period id_investor)
  end=z;
  where period>= 1439 and id_investor not in ('014', '015', '091', '092') or
  period<1439;
  if t{account_number} > ' ' then return;
  select;
   when (portfolio='Mortgage') t{account_number}='Treasury';
   when (portfolio='Resi' and cra_flag='Y') t{account_number}='CRA';
   when (portfolio in('Resi','HELOAN Low/No') and cra_flag='N') 
         t{account_number}='Resi (No CRA)';
   when (portfolio='Specialty') t{account_number}='Specialty';
   otherwise; 
  end;
 keep account_number port_name;
run;

Re: Why my Temp Array unduplication utterly fails vs HASH

Tom — Mon, 04 Nov 2019 03:36:40 GMT

Is there some reason for using a cryptic test for missing character values?

charvar > ' '

Why not use something that is clearer?

not missing(charvar)

charvar ne ' '

Note that the cyptic test will fail if the string is not empty but starts with a character that is before space in the ASCII sequence. Like a TAB.

Re: Why my Temp Array unduplication utterly fails vs HASH

Astounding — Mon, 04 Nov 2019 15:57:41 GMT

No reason, that's just how I've always done it. As a general rule, calling a function takes longer, but I haven't tested for this case.

However, this logic looks a little too complex:

if t{account_number} > ' ' then return;
  select;
   when (portfolio='Mortgage') t{account_number}='Treasury';
   when (portfolio='Resi' and cra_flag='Y') t{account_number}='CRA';
   when (portfolio in('Resi','HELOAN Low/No') and cra_flag='N') 
         t{account_number}='Resi (No CRA)';
   when (portfolio='Specialty') t{account_number}='Specialty';
   otherwise;
   end;

It should probably be simplified to remove the RETURN statement:

if t{account_number} = ' ' then select;
   when (portfolio='Mortgage') t{account_number}='Treasury';
   when (portfolio='Resi' and cra_flag='Y') t{account_number}='CRA';
   when (portfolio in('Resi','HELOAN Low/No') and cra_flag='N') 
         t{account_number}='Resi (No CRA)';
   when (portfolio='Specialty') t{account_number}='Specialty';
   otherwise;  
end;

Is that the spot in the program you are referring to?

Re: Why my Temp Array unduplication utterly fails vs HASH

Tom — Mon, 04 Nov 2019 03:45:21 GMT

I was just asking if there was some special reason why that test would be better than the normal check for non blank value. Note using > will fail if the string starts with a character that is before space in the ASCII coding sequence.

Note I have also seen other posts were someone used

numvar > .

As if it meant not missing. Again that test will fail for special missing since .A to .Z are all large than normal missing value. .Z is the largest missing value.

Re: Why my Temp Array unduplication utterly fails vs HASH

Astounding — Mon, 04 Nov 2019 03:46:37 GMT

I guess whatever is fasted is best ... in this particular case. The program itself assigns all those character values, so I'm not worried about strange values that might appear in the data. But I certainly have been guilty of that in some programs that I have written.

It reminds me of discussions about the range low - high for user-defined character formats. There are definitely cases where you need to be aware of values lower than a blank.