topic Re: file import issue in SAS Programming

file import issue

Amalik — Sat, 21 Jul 2018 01:48:46 GMT

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?

Re: file import issue

Sathish_jammy — Sat, 21 Jul 2018 04:50:22 GMT

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.

Re: file import issue

Tom — Sun, 22 Jul 2018 05:47:42 GMT

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.