BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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,

1 ACCEPTED SOLUTION

Accepted Solutions
rivieralad
Obsidian | Level 7

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

8 REPLIES 8
rivieralad
Obsidian | Level 7

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;

ballardw
Super User

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;
FreelanceReinh
Jade | Level 19

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;
turcay
Lapis Lazuli | Level 10

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

ballardw
Super User

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.

turcay
Lapis Lazuli | Level 10

Thank you ballardw

 

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

 

 

 

ballardw
Super User

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.

turcay
Lapis Lazuli | Level 10

Thank you for your attention, I'll consider it

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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