DATA Step, Macro, Functions and more

How to Count Zero Values If Whole Row Includes Zero Values

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

How to Count Zero Values If Whole Row Includes Zero Values

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,


Accepted Solutions
Solution
‎06-01-2016 01:10 PM
Occasional Contributor
Posts: 19

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

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;

View solution in original post


All Replies
Solution
‎06-01-2016 01:10 PM
Occasional Contributor
Posts: 19

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

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;

Super User
Posts: 10,539

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

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;
Trusted Advisor
Posts: 1,115

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

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;
Super Contributor
Posts: 381

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

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

Super User
Posts: 10,539

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

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.

Super Contributor
Posts: 381

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

Thank you ballardw

 

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

 

 

 

Super User
Posts: 10,539

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

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.

Super Contributor
Posts: 381

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

Thank you for your attention, I'll consider it

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 373 views
  • 4 likes
  • 4 in conversation