Desktop productivity for business analysts and programmers

Exported Excel viewing my numeric columns with blanks as text when try to sort

Reply
Occasional Contributor
Posts: 15

Exported Excel viewing my numeric columns with blanks as text when try to sort

Hello,
I have a table that I am exporting from sas as an excel file.  I've created a workbook on each of my team's computers with a data connection to the excel workbook with the raw exported sas data, which I import as a table under existing connections in excel 2013.  Here's the problem:

This table contain numeric columns with both numbers and blanks.  Some of these columns are differences between these numeric columns containing blanks.  I should clarify that all calculations were preformed in sas and that there are no formulas present in the table or the raw data excel workbook it is referencing. For a specific numeric column that is just raw values and blanks, Excel appears to see these as text when I try to sort.  IE "Sort A to Z" vs. "Sort Largest to smallest".  On my computer, and half of my team's computers, if I unchecked blanks for the numeric raw value column in the filter, It will then see my calculated column as a number and allow me to sort "Largest to smallest".  However the raw data column will still say A to Z for the sort.  For my computer and half my team, when we sort using this a to z it sorts the numbers, but for the other half using the same exact procedure, it sorts as text.  We are all using excel 2013.  I should clarify that all calculations were preformed in sas and that there are no formulas present in the table or the raw data excel workbook it is referencing.


I'm using eg 7.1 and exporting as step in project from the export dropdown in my output tabs.  This works great but is there a way I can constrain how excel views it later.  I could fill all blanks as 0s but a 0 difference means something here and a null means its not applicable.  Can anyone help?



Please let me know.  

Thanks! 

Grand Advisor
Posts: 10,210

Re: Exported Excel viewing my numeric columns with blanks as text when try to sort

How did you export the SAS data?

 

Pleas clarify on "Some of these columns are differences between these numeric columns containing blanks". Do you mean that you calculated a difference in SAS and some of the results were missing values in SAS? Or that two different columns in Excel are behaving differently when they contain blanks?

 

You might look at the originally created output and see if the first rows of the data behaving "correctly (?)" had numeric values in them and the others had blank values.

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 209 views
  • 0 likes
  • 2 in conversation