The SAS Output Delivery System and reporting techniques

Importing CSV to SAS

Reply
N/A
Posts: 0

Importing CSV to SAS

Hi,

When I am trying to import a CSV file to SAS, the data set that gets created is having a blank line at the end (as the last observation) with blank/ missing character/ numeric values. How can I avoid this?

Thanks
Cathy
SAS Super FREQ
Posts: 8,868

Re: Importing CSV to SAS

Posted in reply to deleted_user
Hi:
This is not really an ODS or BASE SAS Reporting procedure (PRINT, REPORT, TABULATE) question. I suspect that the problem is with your CSV file and not with SAS and your import. Probably, if you open the CSV file with Notepad or a Text editor, you will find that there is an empty line, possibly with a few spaces at the very end of the file.

When I typed a file into Notepad and made sure that the cursor was at the end of the last bit of data on the last line as I did my save, SAS imported the file and the number of observations corresponded to the number of data lines that I had typed. However, when I hit the enter key on the last line and hit the space bar a few times before I did the save, THEN, SAS imported the file and the last observation was all missing values.

If the CSV file was created from Excel, then make sure that the area being exported contains only data lines and no empty cells. If the CSV file is created by hand, then make sure that there are no "empty" lines in the CSV file. If you cannot control how the CSV file is created, then you might consider using a program (rather than PROC IMPORT) to read the file, but eliminate the last line with all missing values.

SAS Technical Support can help you with a program like this. To contact Tech Support, go to http://support.sas.com and in the left hand navigation pane click on the link entitled "Submit a Problem".

cynthia
N/A
Posts: 0

Re: Importing CSV to SAS

Posted in reply to Cynthia_sas
IMHO, The "problem" with Excel is that it gives you a workspace of a given size and then populates as much of it with data as are available. So a 4 * 4 table appears as an Excel worksheet with empty space around it, and it is not immediately obvious how much data are really in the sheet. If you go down 100 rows and type in a value, then return home, you will still appear to have a 4 * 4 matrix. Indeed, some Puckish character might construct a spreadsheet with a small matrix and a remote value to confuse a user!!!

SAS on the other hand is quite parsimonious and will build a table constrained to the data that are available, so a 4 * 4 matrix will import as such, my Puckish idea will import as 104 * 4 and all seems well. However, Excel also takes spaces, carriage returns and other concealed characters as valid contents of spreadsheet cells and will define the populated space depending on the byte value of the contents of the cells, and so we can also have 6 * 4 imports if a space is inserted in the sixth row.

You can argue that SAS is doing exactly what it should do. It should not decide to skip blank rows or cells unless I tell it so.

Faced with a situation where Puckish colleagues took data exported to Excel, added required information, and then added some redundant blank rows at the end of the data, I had to find a means whereby the blank rows could be dropped from the table when I re-imported it. It was simple enough to check the Program Data Vector for the table after the import on each row, and where all the values across were blank or missing, some code in the import macro would delete the row.

CSV files are no different from Excel spreadsheets in that regard, and you can scan the rows as well and drop them if that is what is appropriate.

It's a lesson in taking data from free-form data sources into a structured environment. Sometimes it can be problematic.

Kind regards

David
Ask a Question
Discussion stats
  • 2 replies
  • 1312 views
  • 0 likes
  • 2 in conversation