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 |
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
The third column is just with observations, to better explain the logic.
What I need to calculate is the second column (Expected_result).
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
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.