topic Re: ODS Excel output - Missing Numeric Data in ODS and Base Reporting

ODS Excel output - Missing Numeric Data

brittneykp — Wed, 17 Jul 2019 22:23:50 GMT

I am trying to load an Excel workbook with some data for a coworker who is not a SAS Programmer so she can play with it in some Pivot Tables and charts.

I'm using ODS Excel to output the data, because the underlying data has like 30 variables that are coded from a survey and must have formats applied to them to understand them. I've used the OPTIONS MISSING="" to change the numeric missing period to a null, because Excel won't read a period as numeric data.

When I try to import the resulting spreadsheet into PowerPivot, Excel still won't interpret any numeric variable with missing values as numeric. The error says that there is some unknown data in the missing cells that it identifies as character.

Anyone have any ideas on if there is something I can do with the SAS output to ensure the the resulting Excel cells are null? I don't really want to have to use Proc Export because it will take a lot of time to try to recode all the variables.

Re: ODS Excel output - Missing Numeric Data

ballardw — Wed, 17 Jul 2019 22:42:00 GMT

@brittneykp wrote:

I am trying to load an Excel workbook with some data for a coworker who is not a SAS Programmer so she can play with it in some Pivot Tables and charts.

I'm using ODS Excel to output the data, because the underlying data has like 30 variables that are coded from a survey and must have formats applied to them to understand them. I've used the OPTIONS MISSING="" to change the numeric missing period to a null, because Excel won't read a period as numeric data.

When I try to import the resulting spreadsheet into PowerPivot, Excel still won't interpret any numeric variable with missing values as numeric. The error says that there is some unknown data in the missing cells that it identifies as character.

Anyone have any ideas on if there is something I can do with the SAS output to ensure the the resulting Excel cells are null? I don't really want to have to use Proc Export because it will take a lot of time to try to recode all the variables.

It will help to show the exact code you used to export the data to excel since there are multiple possible ways.

If possible it would help to provide some example data in the form of a data step that shows the behavior you are talking about when exported using the same method. Likely you would only need 3 or 4 variables and 3 or 4 rows.

Re: ODS Excel output - Missing Numeric Data

brittneykp — Thu, 18 Jul 2019 14:56:41 GMT

Here is the code I ran:

ods excel file="\\CDSS6PSAS1\ffpb\Reports\Child Trends Final Data Set\Child Trends Data &sysdate. Priority 1.xlsx"
options(sheet_name="data");

options missing="";

PROC PRINT DATA=MATCHED;
ID ch_id ch_subsidy child_weight;
run;

ods excel close;

Here are the first 10 rows of data. I would like the blank cells in "Child Weight" to truly be null. Currently, they have some invisible character in them that Excel reads as character, preventing me from using it as a numeric measure in Excel.

ch_id	ch_subsidy	child_weight	prov_type
			Family-Based Care
			Family-Based Care
			License-Exempt Family Care
			Family-Based Care
			Center-based care
0	C1AP	.13	License-Exempt Family Care
1	C1AP	28.05	Family-Based Care
1	C2AP	303.28	License-Exempt Family Care
1	CSPP	24.29	Center-based care
10	CCTR	82.01	Center-based care

Re: ODS Excel output - Missing Numeric Data

ballardw — Thu, 18 Jul 2019 15:33:14 GMT

The example data needs to be in the form of a data step. I cannot tell from your example whether the Ch_id or child_weight variables are character or not. I am actually only moderately certain that ch_subsidy is character.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: ODS Excel output - Missing Numeric Data

brittneykp — Thu, 18 Jul 2019 15:39:20 GMT

Here is the description of variables. I did not create them, they were given to me, so I don't have a data step to display.

The variable of importance in this question is CHILD_WEIGHT, as it is the blank characters created in the ODS Excel output that are preventing Excel from reading it as numeric.

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
2	ch_id	Char	9	$9.	$9.	Child ID
4	ch_subsidy	Char	4	$4.	$4.	ch_subsidy
3	child_weight	Num	8
1	prov_type	Num	3	PROV_TYPE.		Provider type