topic Remove matching ID using hash in SAS Programming

Remove matching ID using hash

vicky07 — Tue, 07 Jul 2020 19:07:11 GMT

Hello,

I have a dataset called "HAVE" which has 8 phone number columns for each prod ID. There are some invalid phone numbers in this dataset that I want to remove. I also have a list of invalid phones numbers and I am calling it as "invalid_numbers". Basically I want join my "have" against "invalid_numbers" on ID and phone number and if a match found I want to remove that phone number from the have datasaet for that specific ID.

I am new to hashing/array concept and I not getting my desired output. Not sure what I am doing wrong and couldn't it figure out.

Could someone please help? Thanks.

Data have;
input @1 ID :$4. @6 tp1 $10. @17 tp2 $10. @28 tp3 $10. @39 tp4 $10. @50 tp5 $10. @61 tp6 $10. @72 tp7 $10. @83 tp8 :$10.  ;
infile cards missover ;
cards;
1325 5872584458	2478569877	                                         					
5489 8658656767 		              6048795248 
5478 2358984155 							
8799 4897568698                                                                         3697489567
;
run;	



Data invalid_numbers;
input ID :$4. inv_phn $10.;
infile cards;
cards;
1325 5872584458
8799 3697489567
2578 7879565688
3287 9831765765
8974 2478569877
9876 2579375824
;
Quit;

Output:

ID	tp1	tp2	tp3	tp4	tp5	tp6	tp7	tp8
1325		2478569877
5489	8658656767			6048795248
5478	2358984155
8799	4897568698

data want;
set have;
array phn tp1-tp8;
if _N_=1 then do;
dcl hash H(dataset:'invalid_numbers');
H.definekey('ID','inv_phn');
H.definedone();
if 0 then set invalid_numbers;
end;
do over phn;
if H.find() = 0 then call missing(phn);
end;
drop inv_phn;
run;

Re: Remove matching ID using hash

smantha — Tue, 07 Jul 2020 19:24:18 GMT


Data have;
input @1 ID :$4. @6 tp1 $10. @17 tp2 $10. @28 tp3 $10. @39 tp4 $10. @50 tp5 $10. @61 tp6 $10. @72 tp7 $10. @83 tp8 :$10.  ;
infile cards missover ;
cards;
1325 5872584458	2478569877	                                         					
5489 8658656767 		              6048795248 
5478 2358984155 							
8799 4897568698                                                                         3697489567
;
run;	



Data invalid_numbers;
input ID :$4. inv_phn $10.;
infile cards;
cards;
1325 5872584458
8799 3697489567
2578 7879565688
3287 9831765765
8974 2478569877
9876 2579375824
;
run;
data want;
set have;
array phn tp1-tp8;
if _N_=1 then do;
dcl hash H(dataset:'invalid_numbers');
H.definekey('ID','inv_phn');
H.definedone();
if 0 then set invalid_numbers;
end;
do over phn;
 inv_phn=phn;
 if H.find() = 0 then call missing(phn);
end;
drop inv_phn;
run;

Re: Remove matching ID using hash

PeterClemmensen — Tue, 07 Jul 2020 19:42:26 GMT

data want(drop=inv_phn);
   if 0 then set invalid_numbers;
   if _N_ = 1 then do;
      dcl hash h (dataset : "invalid_numbers");
      h.definekey (all : 'Y');
      h.definedone ();
   end;

   set have;
   array tp tp:;
   do over tp;
      if h.check(key : ID, key : tp) = 0 then call missing(tp);
   end;
run;

Re: Remove matching ID using hash

vicky07 — Wed, 08 Jul 2020 19:01:20 GMT

Thanks you very much!!

Re: Remove matching ID using hash

vicky07 — Wed, 08 Jul 2020 19:02:02 GMT

Thank You!!