DATA Step, Macro, Functions and more

file import issue

Reply
Contributor
Posts: 69

file import issue

 

Hi,

 

When I import my data from  excel file to SAS , it shows me character data although the data is numeric.

After inspecting my file, I found out that the cells contain * in them (although I cannot see it as it displays as number)

 The value is shown as a number say 1.12 but when I use ctrl+F and find *, it finds the numbered cell. When I replace * with a blank, it replaces the entire number say 1.12 will replace with a blank.

 

I am unsure on how to handle this weird issue. Any suggestions please?

Contributor
Posts: 39

Re: file import issue

[ Edited ]

Once you import the file, Find out the columns Which being char datatype but it contain numeric values.

 

col1 = input(compress(Column_name,'0123456789.','k')best12.);

Here the converted column contains only numeric and (.) float punctuation.

 

 

Super User
Super User
Posts: 8,289

Re: file import issue

How did you import the file?  Show your code or if you used some point-and-click method explain which one.

 

What type of Excel do you mean?  Are you reading from an XLSX file? An old style XLS file?  Or do you in fact have just simple text file with a .CSV extension and we being tricked into thinking it is an Excel file by your operating systems file associations?

 

If your Excel file has any character values in a column then SAS will assume the whole column is character.  Excel is not a database, so it can cause these types of problems.

 

Your description sounds like what might happen in SAS output if the values was too long to display in the specified space.  Excel can also have similar issues I believe.  But I would expect if you are using XLSX files then it should be less of a problem.

 

Ask a Question
Discussion stats
  • 2 replies
  • 94 views
  • 0 likes
  • 3 in conversation