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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.