How to import data from xls file excluding the strikethrough records in excel?

[sasjourney]

Hi,

How to import a excel sheet after excluding the striked off records. SAS doesn't recognize the strikethrough records while importing the excel file.

Thanks

Ram

[LarryWorley]

Hi Ram,

By 'strikethrough records'. I assume that you mean records where the entire record or key fields have been strikethrough formatted to indicate the record should not be used and that SAS includes these records when you import the spreadsheet. The same issue will happen if you save the spreadsheet in any plain text version -- txt or CSV for example.

Given that assumption, then SAS will not see the strikethrough formatting. It will only see the data.

One work around for this would be to add an indicator column, which would show the row has been struckthrough.  One way is to add a binary valued column, with value of 0 ==> 'not struckthrough' and 1 ==> 'struckthrough'.  Then you could filter on this column.  This then uses data to convey the strikethrough information, rather than the visual information that strikethrough format uses.

You might also try contacting SAS Technical Support to get this added as new functionality in reading from Excel spreadsheets.

[sasjourney]

Thanks Larry

[art297]

Are you on a Windows-based system?  If so, you can probably enact Larry's suggestion, directly, using DDE and automatically identifying strikethrough formats and either doing what you want or adding a strikethrough identifier.