Counting # > 0 in observation across variables

[paulusab]

I have a dataset (example above) that has monthly counts over a 60 month period of time. I want to obtain a count of the number of times the monthly value is > zero across one observation. I don't care to sum variable by variable but to actually know within an observation how many times the reported value is greater than zero across the 60 month period of time. Suggestions?

[Reeza · Accepted solution]

Please post your data as text, not as an image. 

The easiest way to do this is to simply loop over the variables, check if it's >0 and increment a counter. Or you could transpose it to a long format and then calculate it much easier. 

I suspect in the long run the long format is likely to be a better approach to your data. 

data want;
set have;

array _dates(*) oct2013--Mar2014;

count=0;

do i=1 to dim(_dates);
if _dates(i)>0 then count+1;
end;

run;

---

@paulusab wrote:

I have a dataset (example above) that has monthly counts over a 60 month period of time. I want to obtain a count of the number of times the monthly value is > zero across one observation. I don't care to sum variable by variable but to actually know within an observation how many times the reported value is greater than zero across the 60 month period of time. Suggestions?

---

[paulusab]

Thank you, that was very helpful. Now if I want to recall the product of that code in another section of the code how do i refer to it? In PROC print the variable looks like it is named "count" but SAS doesn't recognize that (variable is uninitialized). 

[Reeza]

---

@paulusab wrote:

Thank you, that was very helpful. Now if I want to recall the product of that code in another section of the code how do i refer to it? In PROC print the variable looks like it is named "count" but SAS doesn't recognize that (variable is uninitialized). 

---

I have no idea what that means.

[paulusab]

Data Want;
	set Have;
	array _dates(*) Oct2013--Sep2018;
	count=0;
	do i=1 to dim(_dates);
	if _dates(i)>0 then count+1;
	end;
run; 

The result of this code for observation #1 is 60. All months in the time period have a value greater than 0. I want to now use 60 as a denominator in a segment of code later in this program. But SAS doesn't see the result of the prior program as a permanent variable. It's listed as "count" when I look at the newly created dataset. When I use "count" in the code the log shows "variable count is uninitialized". I'm not sure how to recall the variable to use in the code. Does this make sense?

[Reeza]

Show us how you're trying to use it and where it's not working. The variable only exists in that data set, so your next steps need to reference your WANT data set now, not the HAVE data set.

[paulusab]

Wow, rookie mistake. Thank you!

[Reeza]

It's common enough, that I could guess what was happening without seeing your code though 🙂

[novinosrin]

data have;
array months(6);
input months(*);
cards;
0	4	3	7	6	8
3	7	5	0	5	0
6	8	6	2	8	0
4	7	0	7	9	4
5	8	6	0	5	7
;

data want;
set have;
array t(*) months1--months6;/**group your months vars with a double dash*/
k=compress(catx(',',of t(*)),'0');
want=countw(k);
drop k;
run;

[Reeza]

What happens if a number is 10, 20, 101 etc?

[novinosrin]

Doesn't affect, coz i am counting words 10 would become 1 still counting as greater than zero

data have;
array months(6);
input months(*);
cards;
101	4	30	7	6	8
3	7	20	0	5	0
10	8	6	2	8	0
4	7	0	7	9	4
5	8	6	0	5	7
;

data want;
set have;
array t(*) months1--months6;/**group your months vars with a double dash*/
k=compress(catx(',',of t(*)),'0');
want=countw(k);
drop k;
run;

 hence the reason I used catx instead of cats

[novinosrin]

data have;
array months(6);
input months(*);
cards;
101	4	30	7	6	8
3	7	20	0	5	0
10	8	6	2	8	0
4	7	0	7	9	4
5	8	6	0	5	7
;
data want;
set have;
_t=put(0,rb8.);
array t(*) months1-months6;/**group your months vars with a double dash*/
want = n(of t(*))-count(peekclong (addrlong(t[1]), 48),_t) ;
drop _t;
run;

[Reeza]

@novinosrin What about a custom function via FCMP that can then take an array or list of variables as an argument? I think that's in SASware Ballot or the suggestion of one at least but couldn't find it.

[novinosrin]

Can't agree more. That's what is the difference between a professional thinking(yours) and somebody who merely plays video games using SAS. Kudos to your thoughts.