BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vicky07
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
smantha
Lapis Lazuli | Level 10

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;

View solution in original post

4 REPLIES 4
smantha
Lapis Lazuli | Level 10

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;
vicky07
Quartz | Level 8
Thank You!!
PeterClemmensen
Tourmaline | Level 20
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;
vicky07
Quartz | Level 8
Thanks you very much!!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 574 views
  • 2 likes
  • 3 in conversation