Reading phone data

[vicky07]

Hello,

I receive data from vendor which looks something like below. It has 8 phone number columns(char) from tp1 to tp8 and there are could be missing phone numbers in between for each ID. For instance, I could get a record where tp1 & tp8 columns are populated populated and rest of the phone columns are blank. 

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	                                         					
1325 5872584458			   3697489567 				
5489 8658656767 		              6048795248 
5478 2358984155 							
5478 2358984155 							
8799 4897568698                                                                         3697489567
8799 4897568698 																		3697489567
;
run;	

I want an output that captures all the phone numbers in a single column and dedup them.

Output:
tp
5872584458
2478569877
3697489567
8658656767
6048795248
2358984155
4897568698
3697489567

Thanks in advance for your help!!

[Kurt_Bremser]

Transpose, then sort:

proc transpose
  data=have
  out=want (
    drop=_name_
    rename=(col1=tp)
    where=(tp ne " ")
  )
;
by id;
var tp:;
run;

proc sort data=want nodupkey;
by id tp;
run;

Untested, posted from my tablet.

[ed_sas_member · Accepted solution]

Hi @vicky07 

Here is another approach than the one proposed by @Kurt_Bremser , using an array:

data want (keep=tp);
	set have;
	array _tp (*) tp:;
	do i=1 to dim(_tp);
		if not missing(_tp(i)) then do;
			tp=_tp(i);
			output;
		end;
	end;
run;

proc sort data=want nodupkey;
	by tp;
run;

Best,

[vicky07]

Thank You!!

[Kurt_Bremser]

Since you have multiple observations per ID, the simple transpose approach does not work; use @ed_sas_member's array code.

[vicky07]

Thanks for your time!!