topic Re: phone number compare in SAS Programming

phone number compare

vicky07 — Tue, 05 May 2020 23:45:21 GMT

Hello,

I need help with matching phone numbers from two different data sources. Basically I have a "phn" dataset with list of phone numbers and phone type for each ID and I want to do a compare/vlookup with another table that has valid phone numbers. If a phone number from phn dataset matches with phone number in lookup table then I want to capture that matching phone number along with type for that ID.

Below is my date:

Data phn;
input ph1 $10. @12 type1 $1. ph2 $10. @25 type2 $1. ph3 $10. @38 type3 $1. ph4 $10. @51 type4 $1. @53 ID $5.;
infile cards;
cards;
5872584458 B 2478569877 B 8796532587 C 3698712587 C 78987
9245879578 C 2357895554 B 6048795248 C 2478965479 B 65989
4578956567 B 2358984155 C 2879241533 B 6987858766 C 24789
4897568698 C 2258734789 C 3697489567 B 8972314668 C 23272
3974138688 B 3198766989 B 8658656767 C 8658656767 B 32897
;
run;

Data lookup;
input ph;
infile cards;
cards;
2478569877
6048795248
2357895554
2723450563
8658656767
3697489567
1245784564
2368756875

3198766989
;
run;

My desired output is:

ID ph1 type1 ph2 type2 ph2 type2
78987 2478569877 B
65989 6048795248 C 2357895554 B
24789
23272 3697489567 B
32897 8658656767 B 8658656767 C 3198766989 B

Please note that for ID 32897 I have listed the same phone number twice in my output. The difference is type, one phone number type B and the other has type C.

Thank you for your time and help!!

Re: phone number compare

ChrisNZ — Wed, 06 May 2020 21:53:06 GMT

Is it too much to ask that you check the code you post?

Your code does not load the data properly..

Both inputs statements are erroneous. They should read this:

input ph1 :$10. type1 :$1. ph2 :$10. @25 type2 :$1. ph3 :$10.  type3 :$1. ph4 :$10. type4 :$1. @53 ID :$5.;
input ph $10.;

This works:

data WANT;
  set PHN;
  array INPHN [4] PH1-PH4;
  array INTYP [4] TYPE1-TYPE4;
  array OUTPHN [4] $10;
  array OUTTYP [4] $1;
  if _N_=1 then do;
    dcl hash H(dataset:'LOOKUP');
    H.definekey('PH');
    H.definedone(); 
    if 0 then set LOOKUP;
  end;
  do _I=1 to 4;
    PH=INPHN[_I];   
    if H.find()=0 then do;     
      _FOUND=sum(_FOUND,1); 
      OUTPHN[_FOUND]=INPHN[_I];
      OUTTYP[_FOUND]=INTYP[_I]; 
    end;
  end;
  drop PH: TYP: _:;
  rename OUTPHN1-OUTPHN4 = PHN1-PHN4
         OUTTYP1-OUTTYP4 = TYP1-TYP4 ;
run;

Re: phone number compare

Kurt_Bremser — Wed, 06 May 2020 06:01:02 GMT

Transpose your dataset to long format, and any further coding will be a piece of cake:

data phn;
input
  ph1 :$10.
  type1 :$1.
  ph2 :$10.
  type2 :$1.
  ph3 :$10.
  type3 :$1.
  ph4 :$10.
  type4 :$1.
  ID $5.
;
cards;
5872584458 B 2478569877 B 8796532587 C 3698712587 C 78987
9245879578 C 2357895554 B 6048795248 C 2478965479 B 65989
4578956567 B 2358984155 C 2879241533 B 6987858766 C 24789
4897568698 C 2258734789 C 3697489567 B 8972314668 C 23272
3974138688 B 3198766989 B 8658656767 C 8658656767 B 32897
;

proc sort data=phn;
by id;
run;

proc transpose data=phn out=trans;
by id;
var ph: type:;
run;

data trans2;
set trans;
seq = input(compress(_name_,'','kd'),best.);
_name_ = compress(_name_,'','d');
run;

proc sort data=trans2;
by id seq;
run;

proc transpose data=trans2 out=have (drop=_name_);
by id seq;
id _name_;
var col1;
run;

data lookup;
input ph :$10.;
infile cards;
cards;
2478569877 
6048795248
2357895554
2723450563
8658656767
3697489567
1245784564
2368756875
3198766989
;

data want;
set have;
if _n_ = 1
then do;
  declare hash l (dataset:"lookup");
  l.definekey("ph");
  l.definedone();
end;
if l.check() = 0;
run;

Re: phone number compare

vicky07 — Wed, 06 May 2020 15:42:13 GMT

Sorry about the typo in the input statement. Thank you very much for the solution!!

Re: phone number compare

vicky07 — Wed, 06 May 2020 22:26:12 GMT

Thank you very much!