phone number compare

[vicky07]

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!!

[ChrisNZ · Accepted solution]

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;

[vicky07]

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

[Kurt_Bremser]

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;

[vicky07]

Thank you very much!