Help! Data Format Issue When Exporting from SAS EG to MS Excel 2010

[JeremyW]

I am exporting a lot of Excel files from SAS EG, and using these Excel files to create summary reports.  My 'go-to' tool in Excel is the handy dandy Pivot Table.  Here is my basic issue:

When I use a Pivot Table to "Count" the number of observations within a particular field, the number returned is always equal to the total number of lines in the Excel tab the Pivot Table is sourced from.

Speaking with a few other SAS users in my organization, they are suggesting there are "unprintable" or "hidden" characters which are in those fields, and therefore being counted by the Pivot Table.

I am looking for a way to change my settings, or alter some code, in SAS EG, so that the Excel file being exported comes out without these "unprintable" or "hidden" characters.

Anyone experienced and solved this issue in the past?

[DaveBirch]

Hi JeremyW,

I don't think that it's that there are "unprintable" or "hidden" characters, so much as the way that the count function works in Excel Pivot Tables - it counts cells even when you consider them "missing".

The solution is to sum a field that is either 1 or 0 when you want to provide a "count".

You could use an Excel function to populate the cells in a "Counter" column.  But, it is much neater to generate the column in your SAS dataset.

The datastep statements to produces such "Counters" would be like:

   if CharVar1 = ' ' then CharVar1_Ctr = 0; else CharVar1_Ctr = 1;

   if NumVar1 = . then NumVar1_Ctr = 0; else NumVar1_Ctr = 1;

Hope this helps.