Proc Report - Ignore missing values when computing column

MB_Analyst — Tue, 28 Jan 2020 13:13:40 GMT

I am creating a table that adds a column to sum each row, but if any of the rows included in the equation are missing, the total is then set to missing. How can I ignore missing values and allow the total to still read as the sum of all non-missing columns. I also calculate percent of columns and have the similar issue.

With my current code a row with missing will read:

	Female		Male
Year	Number	%	Number	%	Total
2009	10	33%	20	67%	30
2010	15	.	.	.	.
2011	5	50%	5	50%	10
2012	.	.	10	.	.

I would like the table to read:

	Female		Male
Year	Number	%	Number	%	Total
2009	10	33%	20	67%	30
2010	15	100%	.	0%	15
2011	5	50%	5	50%	10
2012	.	0%	10	100%	10

PROC REPORT DATA=have;
	COLUMNS Year Sex, (n pct) all;
	DEFINE Year / group;
	DEFINE Sex / across  ' ' across;
	DEFINE n / 'Number';
	DEFINE pct / "%" computed format=percent8.;
	DEFINE all / "Total" computed;

	COMPUTE BEFORE Year;
           den = _c2_ + _c4_;
        ENDCOMP;

       COMPUTE pct;
          _c3_ = _c2_ / den;
          _c5_ = _c4_ / den;
       ENDCOMP;

      COMPUTE all;
	 all = _c2_ + _c4_;
     ENDCOMP;	
RUN;

Re: Proc Report - Ignore missing values when computing column

PaigeMiller — Tue, 28 Jan 2020 13:19:37 GMT

Use the SUM function

all = sum( _c2_ , _c4_);

Re: Proc Report - Ignore missing values when computing column

MB_Analyst — Tue, 28 Jan 2020 13:53:50 GMT

I had no idea this was so simple. Thanks!

topic Re: Proc Report - Ignore missing values when computing column in SAS Programming

Proc Report - Ignore missing values when computing column

Re: Proc Report - Ignore missing values when computing column

Re: Proc Report - Ignore missing values when computing column