BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
awardell
Obsidian | Level 7

Hello! I am trying to figure out how to create a data set from a csv file from excel (containing personal information and its updates). The new dataset will contain only the updated personal information. I am using SAS 9.4 

 

The file in excel has variables such as "First_Name Last_name Address_1 Address_2 City State Zip Phone ...... 2_address_1 2_address_2 2_city 2_state 2_zip 2_phone ..... 10_phone" 

where each: phone 2_phone 3_phone... etc is an updated telephone number. 

 

I am trying to create code that will pick the most recent phone number and store it in a variable called "updated_phone". 

 

I know how to create the data set once I get the variables "updated_phone, updated_zip, etc" working, but I am having trouble figuring out an efficient way to make the updated_phone variable. 

 

I have tried it using a huge if then else statement, but I was hoping there is a more efficient way that I am not seeing.  

 

This is what I have for example: (but I would love to do it a way that is more succinct) 

if _10_phone = " " AND _9_phone = " " AND _8_phone = " " AND _7_phone = " "
		AND _6_phone = " " AND _5_phone = " " AND _4_phone = " " AND _3_phone = " "
		AND _2_phone = " " then p_count=1; 
	else if _10_phone = " " AND _9_phone = " " AND _8_phone = " " AND _7_phone = " "
		AND _6_phone = " " AND _5_phone = " " AND _4_phone = " " AND _3_phone = " "
		AND _2_phone ^= " " then p_count =2 ; 
	else if _10_phone = " " AND _9_phone = " " AND _8_phone = " " AND _7_phone = " "
		AND _6_phone = " " AND _5_phone = " " AND _4_phone = " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =3 ; 
	else if _10_phone = " " AND _9_phone = " " AND _8_phone = " " AND _7_phone = " "
		AND _6_phone = " " AND _5_phone = " " AND _4_phone ^= " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =4 ; 
	else if _10_phone = " " AND _9_phone = " " AND _8_phone = " " AND _7_phone = " "
		AND _6_phone = " " AND _5_phone ^= " " AND _4_phone ^= " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =5 ; 
	else if _10_phone = " " AND _9_phone = " " AND _8_phone = " " AND _7_phone = " "
		AND _6_phone ^= " " AND _5_phone ^= " " AND _4_phone ^= " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =6 ; 
	else if _10_phone = " " AND _9_phone = " " AND _8_phone = " " AND _7_phone ^= " "
		AND _6_phone ^= " " AND _5_phone ^= " " AND _4_phone ^= " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =7 ; 
	else if _10_phone = " " AND _9_phone = " " AND _8_phone ^= " " AND _7_phone ^= " "
		AND _6_phone ^= " " AND _5_phone ^= " " AND _4_phone ^= " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =8 ; 
	else if _10_phone = " " AND _9_phone ^= " " AND _8_phone ^= " " AND _7_phone ^= " "
		AND _6_phone ^= " " AND _5_phone ^= " " AND _4_phone ^= " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =9 ; 
	else if _10_phone = " " AND _9_phone ^= " " AND _8_phone ^= " " AND _7_phone ^= " "
		AND _6_phone ^= " " AND _5_phone ^= " " AND _4_phone ^= " " AND _3_phone ^= " "
		AND _2_phone ^= " " then p_count =10 ; 
	
	if p_count=1 then updated_phone= phone; 
	else if p_count=2 then updated_phone = _2_phone; 
	else if p_count=3 then updated_phone = _3_phone; 
	else if p_count=4 then updated_phone = _4_phone; 
	else if p_count=5 then updated_phone = _5_phone; 
	else if p_count=6 then updated_phone = _6_phone; 
	else if p_count=7 then updated_phone = _7_phone; 
	else if p_count=8 then updated_phone = _8_phone; 
	else if p_count=9 then updated_phone = _9_phone; 
	else if p_count=10 then updated_phone = _10_phone; 

 Thanks for all the help! 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

And yet another example of why the typical Excel creation is poorly structured and hard to work with.

If this set were structured:

First_Name Last_name Address_1 Address_2 City State Zip Phone

with a separate row for each additional address, city state zip, phone the exercise is practically trivial.

You do not need to go through any of that to get counts or such

 

data example;
   phone = '111-1111';
_2_phone = '222-2222';
_3_phone = '333-3333';
_4_phone = '444-4444';
_5_phone = '555-5555';
_6_phone = '        ';
_7_phone = '        ';
_8_phone = '        ';
_9_phone = '        ';
_10_phone = '        ';
   
newphone = coalescec(_10_phone,_9_phone,_8_phone,_7_phone,_6_phone,_5_phone,_4_phone,_3_phone,_2_phone,phone);
run;

the COALESCEC (or COALESCE for numeric values) returns the first non-missing value from left to right.

 

View solution in original post

2 REPLIES 2
ballardw
Super User

And yet another example of why the typical Excel creation is poorly structured and hard to work with.

If this set were structured:

First_Name Last_name Address_1 Address_2 City State Zip Phone

with a separate row for each additional address, city state zip, phone the exercise is practically trivial.

You do not need to go through any of that to get counts or such

 

data example;
   phone = '111-1111';
_2_phone = '222-2222';
_3_phone = '333-3333';
_4_phone = '444-4444';
_5_phone = '555-5555';
_6_phone = '        ';
_7_phone = '        ';
_8_phone = '        ';
_9_phone = '        ';
_10_phone = '        ';
   
newphone = coalescec(_10_phone,_9_phone,_8_phone,_7_phone,_6_phone,_5_phone,_4_phone,_3_phone,_2_phone,phone);
run;

the COALESCEC (or COALESCE for numeric values) returns the first non-missing value from left to right.

 

awardell
Obsidian | Level 7

Thank you so much! I have never heard of this command before. I really appreciate it!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 2 replies
  • 602 views
  • 0 likes
  • 2 in conversation