Question about importing CSV file

[dapenDaniel]

I am going to import a CSV file into SAS. In order to avoid data truncation, I made a blank row as the first observation and figure out the longest cell in each column and copy and paste that cell into the blank row. Then I followed the procedures as file --> import data --> CSV file and selected that file and imported it. There is no error message in log. However, I found there are some missing value in the converted SAS file. 

For example

CSV file:

Observation #               Variable 18              Variable 19              Variable 20

15                                      1191                          63                             0

SAS file:

Observation #               Variable 18              Variable 19              Variable 20

15                                      .                                .                                .

What is the problem? How can I fix this issue? 

Thanks in advance!!!

[Kurt_Bremser]

SAS Studio or Enterprise Guide?

Single machine or client/server? Maybe University Edition?

Post the log from that step (use {i}), so we can see with which code SAS tried to read the file.

[ballardw · Accepted solution]

If you use the option GUESSINGROWS=MAX in proc import code there should not be an issue with "truncation", or if using a wizard indicate a very large number such as 32000 (or the number of records in the file) in the "Number of rows to guess" box (look  in the options for the wizard on the "where is the file located" dialog box page.. Be default Proc Import only examines about 20 records to guess which variables are numeric or character and the informat that should be used to read the data, which can cause a number of issues.

Without seeing the exact text file it can be hard to tell why what you think is numeric comes in as missing. One issue with CSV is to NOT use Excel or other spreadsheet to examine file contents as the spreadsheets often reformat values for display. So what you think appears to be a number may actually be some form of character value that does not properly import to numeric. The LOG should have notes about invalid data if it tried to read character values into numeric.

Also note that saving a CSV file after opened in Excel may change the actual contents when saved back to CSV.

Also the LOG should contain the data step code used to read the CSV file. You could copy and paste that into a code box opened with the forums {I} icon from the log. That would show us what SAS thought the file looked like.