topic Re: Counting # > 0 in observation across variables in SAS Procedures

Counting # > 0 in observation across variables

paulusab — Thu, 17 Jan 2019 20:16:39 GMT

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?

Re: Counting # > 0 in observation across variables

Reeza — Thu, 17 Jan 2019 20:20:46 GMT

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?

Re: Counting # > 0 in observation across variables

paulusab — Thu, 17 Jan 2019 21:03:55 GMT

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).

Re: Counting # > 0 in observation across variables

Reeza — Thu, 17 Jan 2019 21:05:57 GMT

@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.

Re: Counting # > 0 in observation across variables

paulusab — Thu, 17 Jan 2019 21:15:42 GMT

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?

Re: Counting # > 0 in observation across variables

Reeza — Thu, 17 Jan 2019 21:16:48 GMT

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.

Re: Counting # > 0 in observation across variables

paulusab — Thu, 17 Jan 2019 21:20:03 GMT

Wow, rookie mistake. Thank you!

Re: Counting # > 0 in observation across variables

Reeza — Thu, 17 Jan 2019 21:26:05 GMT

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

Re: Counting # > 0 in observation across variables

novinosrin — Thu, 17 Jan 2019 21:27:47 GMT

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;

Re: Counting # > 0 in observation across variables

Reeza — Thu, 17 Jan 2019 21:32:45 GMT

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

Re: Counting # > 0 in observation across variables

novinosrin — Thu, 17 Jan 2019 21:38:50 GMT

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

Re: Counting # > 0 in observation across variables

novinosrin — Thu, 17 Jan 2019 22:02:41 GMT

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;

Re: Counting # > 0 in observation across variables

Reeza — Thu, 17 Jan 2019 22:17:41 GMT

@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.

Re: Counting # > 0 in observation across variables

novinosrin — Thu, 17 Jan 2019 22:21:27 GMT

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.