Hi guys,
I am trying to create a continuous loop that will meet the following conditions:
- identify first value>= 4;
- if value>=4 then skip the next 2 records (not relevant for my analysis) and starting from record no 3, count next 5 records; after 5 records add a fixed numeric value (3.14);
- if while counting the 5 records in scope you will find another value>=5 then stop counting, add a fixed numeric value (1.03) and start again from the beginning.
Could you help me find a solution? Please find below an example of the expected result.
Thank you!
Variable | New_column_with_numeric_value | Counter | Explanations |
1 | . | 0 | |
5 | . | 0 | variable >=5 identified;START |
2 | . | 0 | skipp first 2 records |
3 | . | 0 | skipp first 2 records |
1 | . | 1 | start counting |
6 | 1.03 | 0 | add value for counter<5, reset counter and start againg |
1 | . | 0 | skipp first 2 records |
3 | . | 0 | skipp first 2 records |
2 | . | 1 | start counting |
1 | . | 2 | |
3 | . | 3 | |
3 | . | 4 | |
1 | 3.14 | 5 | counter=5 then 3.14; STOP |
1 | . | 0 | wait for next variable>=5 |
2 | . | 0 | wait for next variable>=5 |
6 | . | 0 | variable >=5 identified;START |
1 | . | 0 | skipp first 2 records |
2 | . | 0 | skipp first 2 records |
3 | . | 1 | start counting |
1 | 2 | ||
1 | 3 | ||
1 | 4 | ||
2 | 3.14 | 5 | counter=5 then 3.14; STOP |
Hi @Vic6 and welcome to the SAS Support Communities!
Is the change of criteria from "value>=4" in item 1 and 2 to "value>=5" in item 3 and "variable >=5" in column "Explanations" really part of the rules or just a typo? I assumed the latter and used the threshold 5 consistently. For the test data it doesn't matter anyway (as value 4 doesn't occur).
/* Create test data for demonstration */
data have;
input Variable @@;
cards;
1 5 2 3 1 6 1 3 2 1 3 3 1 1 2 6 1 2 3 1 1 1 2
;
/* Create variables New and Counter */
data want(drop=_:);
set have;
if variable>=5 then
if _s then do;
New=1.03;
_c=0;
end;
else _s=1;
if _s then _c+1;
Counter=max(0,_c-3);
if Counter=5 then do;
New=3.14;
_s=0;
_c=0;
end;
retain _s;
run;
Hi @Vic6 and welcome to the SAS Support Communities!
Is the change of criteria from "value>=4" in item 1 and 2 to "value>=5" in item 3 and "variable >=5" in column "Explanations" really part of the rules or just a typo? I assumed the latter and used the threshold 5 consistently. For the test data it doesn't matter anyway (as value 4 doesn't occur).
/* Create test data for demonstration */
data have;
input Variable @@;
cards;
1 5 2 3 1 6 1 3 2 1 3 3 1 1 2 6 1 2 3 1 1 1 2
;
/* Create variables New and Counter */
data want(drop=_:);
set have;
if variable>=5 then
if _s then do;
New=1.03;
_c=0;
end;
else _s=1;
if _s then _c+1;
Counter=max(0,_c-3);
if Counter=5 then do;
New=3.14;
_s=0;
_c=0;
end;
retain _s;
run;
Many thanks! It works great!
One more question. What if I want to skip the first 2 records (from the example above) even if the value =>5? The idea is that the first 2 records are irrelevant regardless the value.
You can extend the first IF-THEN statement as follows to ignore values >=5 in the first two records:
if _n_>2 & variable>=5 then
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.