Hi experts, I have the attached data. I want to create another variable based on some criteria. The new variable High will have 1 if id (Co_per_role) score more than 100 percent (>1) in the confidence column/variable and show this same behavior at least twice in the sample period; I will assign 1 to high from the first time such behavior shows up else it will be 0.
For instance;
co_per_rol year confidence High
1234 2000 .87 0
1234 2001 .78 0
1234 2002 .45 0
1235 2000 .5 0
1235 2001 1.45 1
1235 2002 .64 1
1235 2003 1.2 1
1236 2000 .67 0
1236 2001 1.23 0
1236 2002 .54 0
Hi @Ramin1,
If your dataset is grouped by CO_PER_ROL and sorted by YEAR within the CO_PER_ROL BY-groups, you can use a double DOW loop:
data want(drop=_:);
do _n=1 by 1 until(last.co_per_rol);
set sample;
by co_per_rol notsorted;
if confidence>1 then do;
if _y=. then _y=year;
_c=sum(_c,1);
end;
end;
do _n=1 to _n;
set sample;
High=_c>1 & year>=_y;
output;
end;
run;
> show this same behavior at least twice in the sample period
The behaviour only happens once in your example: once per year.
Also please post your data as code so we can easily use it.
You should define exactly what "sample period" means if it has any bearing on the problem.
I suspect you are looking for something like this:
data want; set sample; by co_per_rol; retain high; if first.co_per_rol then high=0; high = max(high, confidence >0); run;
This assumes the data is actually sorted by co_per_rol and year.
Retain keeps the value of a variable across data step boundaries.
The BY statement creates automatic variables indicating whether the current record is the first or last of a by group that is 1 (true) or 0 (false) and can be used to reset a variable for the first record.
Max function returns the largest value, so if High has been set to 1 it stays1. The comparison Confidence > 0 will return 1 when true or 0 when false.
Pick @FreelanceReinh 's reply as the answer then please.
Hi @Ramin1,
If your dataset is grouped by CO_PER_ROL and sorted by YEAR within the CO_PER_ROL BY-groups, you can use a double DOW loop:
data want(drop=_:);
do _n=1 by 1 until(last.co_per_rol);
set sample;
by co_per_rol notsorted;
if confidence>1 then do;
if _y=. then _y=year;
_c=sum(_c,1);
end;
end;
do _n=1 to _n;
set sample;
High=_c>1 & year>=_y;
output;
end;
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.