topic Re: How to Count Zero Values If Whole Row Includes Zero Values in SAS Programming

How to Count Zero Values If Whole Row Includes Zero Values

turcay — Wed, 01 Jun 2016 14:33:49 GMT

Hello everybody,

I have a question for you. I want to get number of zero values if whole row has zero values. I prepared a sample code as below. I tried to do some methods and looked for the old discussion in this site but I couldn't find and create a solution. Can somebody help me, please?

Data Have;
Length Numeric1 8 Numeric2 8 Numeric3 8 Numeric4 8 Numeric5 8;
Infile Datalines Missover;
Input Numeric1 Numeric2 Numeric3 Numeric4 Numeric5;
Datalines;
0 0 0 0 0 /* Count 1*/
-1 0 0 0 -1
2 2 0 -2 -2
0 0 0 0 0
-9 9 6 5 -11
0 0 0 0 0 /* Count 2*/
;
Run;
ODS OUTPUT Summary=Want2 (Rename=(Mean=_Mean));

Proc Means Data=Have Mean StackOdsOutput;
Var Numeric1-Numeric5;
Run;

PROC SQL;
Create Table Want As
Select Count(_Mean) As CountOfVariables From Want2
Where _Mean=0;
QUIT;

In my foregoing sample, it should count "2" value.

Thank you,

Re: How to Count Zero Values If Whole Row Includes Zero Values

rivieralad — Wed, 01 Jun 2016 14:48:56 GMT

A simple solution is to use max and min functions...you need both to ensure all values are zero, as below.

Looking at your data I think the end result should be three, not two, as per the code below.

Comment out "if eof then..." if you want to keep each line.

Cheers

Chris

Data Have;
Length Numeric1 8 Numeric2 8 Numeric3 8 Numeric4 8 Numeric5 8;
Infile Datalines Missover;
Input Numeric1 Numeric2 Numeric3 Numeric4 Numeric5;
Datalines;
0 0 0 0 0 /* Count 1*/
-1 0 0 0 -1
2 2 0 -2 -2
0 0 0 0 0 /* Count 2*/
-9 9 6 5 -11
0 0 0 0 0 /* Count 3*/
;
Run;

data want (keep = zero_count);
   set have end = eof;
   retain zero_count 0;
   if max(of numeric1-numeric5) eq 0 and min(of numeric1-numeric5) eq 0 then
       zero_count + 1;
   if eof then
       output;
run;

Re: How to Count Zero Values If Whole Row Includes Zero Values

ballardw — Wed, 01 Jun 2016 14:52:44 GMT

I think that you'll need to add an additional variable to your have set.

Also why is the 4th line not counted? If has 5 zero values.

You do not specify what to do if if 4 of the 5 are zero and the 5th is missing or other combinations of 0 and missing.

This adds a variable that is 1 when all 5 values are valued 0 and 0 other wise. Sum it to get a count of the zero rows.

Or you could conditionally assign the 1 and leave missing otherwise to use N to count.

Data Have;
   Length Numeric1 8 Numeric2 8 Numeric3 8 Numeric4 8 Numeric5 8;
   Infile Datalines Missover;
   Input Numeric1 Numeric2 Numeric3 Numeric4 Numeric5;
   CountZero = (n(of Numeric:)=5)*(max(of Numeric:)=0)*(Min(of numeric:)=0);
Datalines;
0 0 0 0 0 
-1 0 0 0 -1
2 2 0 -2 -2
0 0 0 0 0
-9 9 6 5 -11
0 0 0 0 0 
;
Run;

Re: How to Count Zero Values If Whole Row Includes Zero Values

FreelanceReinh — Wed, 01 Jun 2016 15:45:06 GMT

Here's a similar solution, counting observations with zeros in all variables whose names start with "Num":

data want(keep=c0);
set have end=eof;
c0+ ~uss(of Num:, nmiss(of Num:));
if eof;
run;

Re: How to Count Zero Values If Whole Row Includes Zero Values

turcay — Wed, 01 Jun 2016 17:10:08 GMT

Hello,

Rivieralad, yes you are right, the result shoud be three and your code provides me desired output. I also get the percentage value as below.

Data Have;
Length Numeric1 8 Numeric2 8 Numeric3 8 Numeric4 8 Numeric5 8;
Infile Datalines Missover;
Input Numeric1 Numeric2 Numeric3 Numeric4 Numeric5;
Datalines;
0 0 0 0 0 /* Count 1*/
-1 0 0 0 -1
2 2 0 -2 -2
0 0 0 0 0 /* Count 2*/
-9 9 6 5 -11
0 0 0 0 0 /* Count 3*/
; 
Run;
 
Data Want(Keep=Zero_Count Percent) ; 
Set Have End=Eof;
Retain Zero_Count 0;
If Max(of numeric1-numeric5) eq 0 and Min(of numeric1-numeric5) eq 0 then
Zero_Count + 1;
Percent=Zero_Count/_n_; 
If eof Then 
Output;
run;

FreelanceReinhard and ballardw also thank you for your responses

Re: How to Count Zero Values If Whole Row Includes Zero Values

ballardw — Wed, 01 Jun 2016 17:21:25 GMT

be aware that solution will count

0 . . . .

0 0 . . .

0 0 0 . .

0 0 0 0 .

If you never have missing values or that is the desired case then okay. But you should be aware for when you reuse this code elsewhere and the data condition changes you may get undesired results.

Re: How to Count Zero Values If Whole Row Includes Zero Values

turcay — Wed, 01 Jun 2016 17:24:07 GMT

Thank you ballardw

I don't have missing values should I still consider to my output?

Re: How to Count Zero Values If Whole Row Includes Zero Values

ballardw — Wed, 01 Jun 2016 17:33:14 GMT

No missing values then you should be okay. I just wanted you to realize that later you might run into an issue if you do have missing values.

Re: How to Count Zero Values If Whole Row Includes Zero Values

turcay — Wed, 01 Jun 2016 21:22:55 GMT

Thank you for your attention, I'll consider it