topic Re: Replacing hash table in SAS Programming

Replacing hash table

astha8882000 — Thu, 12 Jul 2018 16:41:32 GMT

Hello,

I have a reference code and I want to remove hash table out of it since I'm new to SAS and it is adding a lot of complication and errors when I run the code with it. Here is a sample of what I'm doing:

assuming dataset ‘ds1’ looks like the following:

phoneno	accno	amt	custno	dda
1234567601	10234567601	10	65463	454646328
1234567602	21234567601	2000	545	8769646328
1234567603	32234567601	456856	5345	87634646328
1234567604	43234567601	5858	34545	3469646328
1234567605	54234567601	56567	34	99646328
1234567606	65234567601	575	32	79769646328
1234567607	76234567601	45	324646	239646328
1234567608	87234567601	45	121	6789646328
1234567609	98234567601	45	432	87696463458
1234567610	99234567601	452332	13421	9869646328

My code:

data ds2;

if _n_=1 then do;

dcl hash hh (dataset: 'ds1');

hh.definekey ('phoneno');

hh.definedone();

end;

keep address zip phoneno dda tdda;

%INCLUDE INCLF(f1.sas); //code for f1 provided below

if hh.find()=0;

%INCLUDE INCLF(f2.sas); //code for f2 provided below

Run;

F1 code:

Attrib

FILEDATE; –this is a variable in ds3

Set ds3 end=eof;

IF CUBL > 000 –this is a variable in ds3

AND DDA NOT IN ('28378','657479') ;

Keep _All_ ;

F2 code:

Length TDDA $ 1;

If DDA in (‘99646328’ ‘9964632823)Then

TDDA = 'Y';

Else

TDDA = 'N';

Re: Replacing hash table

Tom — Thu, 12 Jul 2018 16:52:11 GMT

Why not just do this?

proc sql ;
create table ds2 as
select
  address
, zip
, phoneno
, dda
, case when DDA in ('99646328' '9964632823') then 'Y' else 'N' end as tdda
, *
from ds3
where cubl > 0
  and dda not in ('28378' '657479')
  and phoneno in (select phoneno from ds1)
;
quit;

Re: Replacing hash table

astha8882000 — Thu, 12 Jul 2018 17:04:33 GMT

Thank you! Based on both of these codes I have a question

As per my code, F2 should be run only when hh.find()=0, this condition is replaced by what in your code?

Re: Replacing hash table

Tom — Thu, 12 Jul 2018 17:07:14 GMT

Your original data step is using a subsetting IF statement. So it is only keeping the observations where the phone number is found.

So this part of the WHERE clause implements that condition.

AND phoneno in (select phoneno from ds1)