topic Including 0 as a Mean for When Data is Missing in SAS Programming

Including 0 as a Mean for When Data is Missing

BCNAV — Fri, 10 Jul 2020 19:01:37 GMT

I am creating a dataset for an Excel pivot table with respect to revenue from airlines. There are cases where an airline has no flights for a month, and hence no revenue. I'd like there to be a forced count of 0 for the month and 0 for revenue. I have this code from proc means:

PROC MEANS DATA=EGTASK.REVENUE
	NOPRINT	CHARTYPE
	NOLABELS
	NWAY
	
		SUM NONOBS 	;
	VAR amount;
	CLASS name /	ORDER=UNFORMATTED ASCENDING;
	BY Year Month;

OUTPUT 	OUT=EGTASK.REVENUE_BY_CLIENT_MONTHLY
	
		SUM()=

	/ AUTONAME AUTOLABEL  WAYS INHERIT
	;
RUN;

What ends up happening is that the top 6 clients (called name above) from the previous year are tracked during the current year. If there is a month where there are no observations for a client (called name) then they should be set to 0 for both flight counts and revenue totals.

thanks

Re: Including 0 as a Mean for When Data is Missing

ballardw — Fri, 10 Jul 2020 19:33:34 GMT

If you want to use Proc Means then you will have to add the zero values into the data set if you want the mean to treat the value as 0 for means calculations.

Or perhaps you might want the year and month as class variables and use COMPLETETYPES on the proc statement to force values for months without any actual observations to appear in the output. Not really very clear what you actually expect.

Re: Including 0 as a Mean for When Data is Missing

Reeza — Fri, 10 Jul 2020 19:38:55 GMT

You can use a CLASSDATA to indicate the levels you like to have in the report.

Re: Including 0 as a Mean for When Data is Missing

BCNAV — Sat, 11 Jul 2020 19:54:21 GMT

CLASSDATA dataset does it! Thanks!