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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.