DATA Step, Macro, Functions and more

Importing Phone Numbers from a CSV File

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Importing Phone Numbers from a CSV File

Hello, 

I am brand new to SAS so I have a basic question. 

 

I am practicing reading in CSV files into SAS using an infile statement, and then cleaning that data to make it into a standardized format. 

 

The data I want to read into SAS is a list of phone numbers: 

 

Raw Data Looks Like This: 

Phone 

(908)232-4856

210.343.4757

(516) 343 - 9293

9342342345

 

I tried to import using the below program, but my problem is that the phone numbers are getting cut off at the dash (-) or the dot (.) or the blank. How do I read in everything properly? What I am I missing? Any help much appreciated. Thank you! 

 

/*This is how I am reading in the file into SAS*/
data phone;
infile '/sscc/home/m/mkh246/Textbook_Datasets/Listing of Phone.csv' dlm=',' firstobs=2 truncover;
input phone $;
Run;

 

/*This is my data cleaning step I am practicing*/
data phoneformatted;
length PhoneNumber $10;
set work.phone;
PhoneNumber=compress(Phone,'()-.');
drop Phone;
Run;

 

 


Accepted Solutions
Solution
‎02-10-2016 01:58 PM
Respected Advisor
Posts: 4,649

Re: Importing Phone Numbers from a CSV File

You are very close. Try this:

 

/*This is how I am reading in the file into SAS*/
data phone;
infile '/sscc/home/m/mkh246/Textbook_Datasets/Listing of Phone.csv' dlm=',' firstobs=2 truncover;
length phone $16;
input phone &;
Run;
 
/*This is my data cleaning step I am practicing*/
data phoneformatted;
length PhoneNumber $10;
set work.phone;
PhoneNumber=compress(Phone, ' ()-.');
drop Phone;
Run;

 Don't let SAS guess the length of your input field.The & in the input will allow single spaces in the input field. Add a space in the list of characters to compress.

PG

View solution in original post


All Replies
Super User
Posts: 17,837

Re: Importing Phone Numbers from a CSV File

SAS strings by default are assigned a length of 8, unless you specify a longer length. You'll need to specify a longer length ahead of reading in the variable, an informat is a simple way.

 

Is your file actually CSV (comma separated)? The way presented it's shown as a single variable and then you may have to change how you read it in. 

 

data phone;
Informat phone $20.;
infile '/sscc/home/m/mkh246/Textbook_Datasets/Listing of Phone.csv' dlm=',' firstobs=2 truncover;
input phone $;
Run;

 

Solution
‎02-10-2016 01:58 PM
Respected Advisor
Posts: 4,649

Re: Importing Phone Numbers from a CSV File

You are very close. Try this:

 

/*This is how I am reading in the file into SAS*/
data phone;
infile '/sscc/home/m/mkh246/Textbook_Datasets/Listing of Phone.csv' dlm=',' firstobs=2 truncover;
length phone $16;
input phone &;
Run;
 
/*This is my data cleaning step I am practicing*/
data phoneformatted;
length PhoneNumber $10;
set work.phone;
PhoneNumber=compress(Phone, ' ()-.');
drop Phone;
Run;

 Don't let SAS guess the length of your input field.The & in the input will allow single spaces in the input field. Add a space in the list of characters to compress.

PG
New Contributor
Posts: 4

Re: Importing Phone Numbers from a CSV File

Thank you PG! This worked! 

 

I see how the "&" works, but may I ask where does the $16 come from in the length? I am struggling a little bit wrapping my head around how the length part works exactly. If you could explain how that works that would be great! 

 

Thank you! 

 

 

Respected Advisor
Posts: 4,649

Re: Importing Phone Numbers from a CSV File

The length statement tells SAS what the length of the character variable should be, instead of letting SAS guessing (sometimes wrong) what it should be. The length statement should precede the first mention of the variable in the data step. I used 16 but, of course, you could chose another length.

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 217 views
  • 2 likes
  • 3 in conversation