BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2276 views
  • 0 likes
  • 2 in conversation