BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
paulusab
Calcite | Level 5

Untitled.png

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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:

Untitled.png

 

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?


 

View solution in original post

13 REPLIES 13
Reeza
Super User

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:

Untitled.png

 

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
Calcite | Level 5

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
Super User

@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
Calcite | Level 5
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
Super User
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
Calcite | Level 5

Wow, rookie mistake. Thank you!

Reeza
Super User
It's common enough, that I could guess what was happening without seeing your code though 🙂
novinosrin
Tourmaline | Level 20
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
Super User
What happens if a number is 10, 20, 101 etc?
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20
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
Super User

@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
Tourmaline | Level 20

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. 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 13 replies
  • 6942 views
  • 7 likes
  • 3 in conversation