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

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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,

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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
Meteorite | Level 14

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
Quartz | Level 8
Thank You!!
vicky07
Quartz | Level 8
Thanks for your time!!

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
  • 5 replies
  • 582 views
  • 0 likes
  • 3 in conversation