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;
Use the SUM function
all = sum( _c2_ , _c4_);
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.