topic Re: Count values that meet specific requirements in SAS Programming

Count values that meet specific requirements

Vic6 — Wed, 20 Oct 2021 15:25:06 GMT

Hi guys,

I'm trying to find a solution for the following issue. In the table below, I want to count the number of values from each continuous string of values that are higher than 1.

Variable_1	Expected_result
1	0
1	0
2	0
3	2	(String of 2 consecutive values>1)
1	0
5	0
6	0
3	0
2	0
5	0
4	6	(String of 6 consecutive values>1)
1	0
2	1
1	0
3	0
2	2	(String of 2 consecutive values>1)
1	1

Re: Count values that meet specific requirements

Reeza — Wed, 20 Oct 2021 15:29:29 GMT

Is the third column what you expect as output? It would probably help to show at least one use case that had more than one value in a row and the expected output in that scenario.

Re: Count values that meet specific requirements

Vic6 — Wed, 20 Oct 2021 15:33:54 GMT

The third column is just with observations, to better explain the logic.

What I need to calculate is the second column (Expected_result).

Re: Count values that meet specific requirements

yabwon — Wed, 20 Oct 2021 18:41:16 GMT

Maybe like this:

data have;
input x;
cards;
1
1
2
3
1
5
6
3
2
5
4
1
2
1
3
2
1
;
run;


/* format for constructing groups */
proc format;
value gtOne
  1<-High = 1
  other  = 0
  ;
run;

data want;
  format x gtOne.;
  set have;
  by x groupformat notsorted;

  if first.x then i = 0; drop i;

  i + x>1; /* counter for group */

  if last.x and x>1<i /* condition for group */
    then y = i; 
  else y = 0;
run;
proc print data = want;
  format x; /* drop format from x */
run;

Bart

Re: Count values that meet specific requirements

Ksharp — Thu, 21 Oct 2021 11:56:52 GMT

data have;
input x;
cards;
1
1
2
3
1
5
6
3
2
5
4
1
2
1
3
2
1
;
run;

data temp;
 set have;
 if x>1 then count+1;
  else count=0;
group=(count=0);
run;
data want;
 set temp;
 by group notsorted;
 if not last.group then count=0;
 drop group;
run;