Creating a new data set with "updated" personal information from a csv file

awardell — Tue, 26 Jun 2018 18:04:12 GMT

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!

Re: Creating a new data set with "updated" personal information from a csv file

ballardw — Tue, 26 Jun 2018 19:14:40 GMT

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.

Re: Creating a new data set with "updated" personal information from a csv file

awardell — Tue, 26 Jun 2018 19:34:09 GMT

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

topic Re: Creating a new data set with "updated" personal information from a csv file in SAS Programming

Creating a new data set with "updated" personal information from a csv file

Re: Creating a new data set with "updated" personal information from a csv file

Re: Creating a new data set with "updated" personal information from a csv file