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!!!
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.
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.
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.