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!!
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,
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.
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,
Since you have multiple observations per ID, the simple transpose approach does not work; use @ed_sas_member's array code.
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.