topic Re: Count the number of non-zero occurrences for a variable in SAS Data Science

Count the number of non-zero occurrences for a variable

bpearce — Thu, 30 Jul 2015 14:12:08 GMT

I'm working with a very large data set (over 15 million observations and over 1000 variables). I would like to count the number of non-zero entries for a handful of variables from within my data set. For example, in the table below VAR 1 has 3 occurrences, and VAR 2 has two occurrences. I'm a beginner, so please provide some explanation if possible. I appreciate any help!

Client #	Var 1	Var 2
1	245	2699
2	0	0
3	3694	0
4	1548	0
5	0	3554

Re: Count the number of non-zero occurrences for a variable

M_Maldonado — Thu, 30 Jul 2015 14:20:56 GMT

If you have SAS IML, there is one really fast way to count missing/not missing.

Look at this post: http://blogs.sas.com/content/iml/2011/09/19/count-the-number-of-missing-values-for-each-variable.html

Do you have HP procs and a grid license? That is another route.

I hope it helps,

Miguel

Re: Count the number of non-zero occurrences for a variable

Steelers_In_DC — Thu, 30 Jul 2015 14:29:43 GMT

I'm sure there will be some good array solutions, but this is the first thing that came to mind. If it is only a handful of variables it's a viable solution.

data have;

infile cards dsd;

input Client Var1 Var2;

cards;

1,245,2699

2,0,0

3,3694,0

4,1548,0

5,0,3554

;

run;

data want;

set have;

count = sum((var1 ne 0)+(var2 ne 0));

run;

Re: Count the number of non-zero occurrences for a variable

RW9 — Thu, 30 Jul 2015 14:47:03 GMT

Hi,

Yes, you can use arrays. This would be preferred if there were many variables.

data have;

client=1; var1=245; var2=2696; output;

client=2; var1=0; var2=0; output;

run;

data want (drop=i);

set have;

array list{*} var1 var2;

count_miss=0;

do i=1 to dim(list);

if list{i}=0 then count_miss=sum(count_miss,1);

end;

run;

If they all have the same prefix then its even easier:

data want (drop=i);

set have;

array var{*};

count_miss=0;

do i=1 to dim(var);

if var{i}=0 then count_miss=sum(count_miss,1);

end;

run;

Re: Count the number of non-zero occurrences for a variable

Steelers_In_DC — Thu, 30 Jul 2015 14:56:53 GMT

RW9, I'm a beginner at arrays. I ran what you have and get the following error. Can you assist?

22	GOPTIONS ACCESSIBLE;
23	data want2 (drop=i);
24	set have;
25	array var{*};

ERROR: The array var has been defined with zero elements.

26	count_miss=0;
27	do i=1 to dim(var);
28	if var{i}=0 then count_miss=sum(count_miss,1);

ERROR: Too many array subscripts specified for array var.

29	end;
30	run;

Re: Count the number of non-zero occurrences for a variable

bpearce — Fri, 31 Jul 2015 13:20:54 GMT

Hi RW9,

I ran the code, but I'm not getting what I had hoped for. The code ran the number of misses (zero values), by row. For example, client 1 would have 0 misses and client 2 would have 2 misses. However, I want the total number of times each variable (column) has a value. In my real data set, I have over 15 million clients and 1000 variables. I want separate totals for each variable, so variable 1 would have 8 million occurrences and variable 2 would have 9 million (as an example). Can you help me modify the code?

Thanks,

Re: Count the number of non-zero occurrences for a variable

Steelers_In_DC — Fri, 31 Jul 2015 13:51:34 GMT

I thought you were checking "a handful" of variables. Do you need to check 1000 now? If so are they similarly named, as in VAR1 VAR2...VAR1000?

Re: Count the number of non-zero occurrences for a variable

RW9 — Fri, 31 Jul 2015 14:01:34 GMT

Ah, ok, so you want to check if the sum per column is 0. Then your best off checking one of the procedures associated with that. Proc means, or proc summary. Say:

proc means data=have;

var var1-var1000;

output out=results mean=mean;

run;

Then if the resulting mean for the variable is 0 you know they are all zeroes.

Re: Count the number of non-zero occurrences for a variable

bpearce — Fri, 31 Jul 2015 14:30:37 GMT

I am only checking a handful of them. They are not similarly named. I think RW9's response did the trick.

Re: Count the number of non-zero occurrences for a variable

ballardw — Fri, 31 Jul 2015 14:37:51 GMT

A different approach:

Use a custom format to identify ranges of values of interest. This one treats every non-missing value except 0 as nonzero and shows 0 as such.

proc format;

value nonzero

low - <0, 0<-high = 'Non-zero'

0 = 'Zero';

run;

_numeric_ is a special variable list identifier that says to do what ever for all numeric variables. So can be a short hand for large lists.

Using proc freq and the format will give you nonzero and zero counts and percentages for each variable plus the missing by default.

proc freq data=have;

tables _numeric_;

format _numeric_ nonzero. ;

run;