Hello All, Could you do me a great favor to mark differnent results for each group? If count >=1 for at least 4 consecutive years, then result= ‘pass’ for these years, else result= ‘fail’. Thanks very much in advance.
data have;
input group $ year count;
datalines;
A 2002 8.6
A 2003 6.3
A 2004 2.3
A 2005 4.6
A 2006 2.3
A 2007 4.6
B 2000 0.4
B 2001 1.9
B 2002 1.1
B 2003 1.0
B 2004 1.6
B 2005 1.5
B 2006 0.7
C 2004 4.8
C 2005 4.0
C 2006 4.5
C 2007 3.7
D 2001 4.0
D 2002 6.1
D 2003 3.5
E 2001 1.5
E 2002 0.6
F 2004 0.8
;
run;
The output should be:
group | year | count | result |
A | 2002 | 8.6 | pass |
A | 2003 | 6.3 | pass |
A | 2004 | 2.3 | pass |
A | 2005 | 4.6 | pass |
A | 2006 | 2.3 | pass |
A | 2007 | 4.6 | pass |
B | 2000 | 0.4 | fail |
B | 2001 | 1.9 | pass |
B | 2002 | 1.1 | pass |
B | 2003 | 1.0 | pass |
B | 2004 | 1.6 | pass |
B | 2005 | 1.5 | pass |
B | 2006 | 0.7 | fail |
C | 2004 | 4.8 | pass |
C | 2005 | 4.0 | pass |
C | 2006 | 4.5 | pass |
C | 2007 | 3.7 | pass |
D | 2001 | 4.0 | fail |
D | 2002 | 6.1 | fail |
D | 2003 | 3.5 | fail |
E | 2001 | 1.5 | fail |
E | 2002 | 0.6 | fail |
F | 2004 | 0.8 | fail |
Can we assume a maximum of 8 observations per GROUP? It would certainly make the programming easier.
First, thank you for providing data in a sas program form.
The technique I would recomend is to
data want (drop=I pf list4 Y rslt:);
array rslt{2000:2008}; /* use min and max expected year for array bounds */
/* Read a group and inspect its sequence of counts*/
do I=1 by 1 until (last.group);
set have;
by group;
pf= ifc(count>=1,'P','F');
list4=cats(lag3(PF),lag2(PF),lag(PF),PF);
if I>=4 and list4='PPPP' then do Y=year-3 to year;
rslt{Y}=1;
end;
end;
do until (last.group);
set have;
by group;
result=ifc(rslt{Year}=1,'PASS','FAIL');
output;
end;
run;
data have;
input group $ year count;
flag=count ge 1;
datalines;
A 2002 8.6
A 2003 6.3
A 2004 2.3
A 2005 4.6
A 2006 2.3
A 2007 4.6
B 2000 0.4
B 2001 1.9
B 2002 1.1
B 2003 1.0
B 2004 1.6
B 2005 1.5
B 2006 0.7
C 2004 4.8
C 2005 4.0
C 2006 4.5
C 2007 3.7
D 2001 4.0
D 2002 6.1
D 2003 3.5
E 2001 1.5
E 2002 0.6
F 2004 0.8
;
run;
data want;
do i=1 by 1 until(last.flag);
set have;
by group flag notsorted;
end;
do until(last.flag);
set have;
by group flag notsorted;
if i ge 4 and flag=1 then result='pass ';
else result='fail';
output;
end;
drop i flag;
run;
And another NOT so smart method, but just for fun;
data want;
if _N_ = 1 then
do;
length _flag_min _flag_max 8;
declare hash myhash(multidata:'YES' );
myhash.defineKey('group');
myhash.defineData('group','_flag_min','_flag_max');
myhash.defineDone( );
call missing(_flag_min, _flag_max);
end;
do n=1 by 1 until(last.group);
set have end=last;
by group;
if first.group then _flag=0;
if count>=1 then _flag+1;
else _flag=0;
if _flag=1 then _flag_min=n;
else if _flag=4 then
do;
_flag_max=n;
myhash.add();
end;
else if _flag>4 then
do;
_flag_max=n;
myhash.add();
end;
end;
do n=1 by 1 until(last.group);
set have end=last;
by group;
result='FAIL';
rc = myhash.find();
if (rc = 0) then do;
if _flag_min<=n<=_flag_max then result='PASS';
rc = myhash.find_next();
do while(rc = 0);
if _flag_min<=n<=_flag_max then result='PASS';
rc = myhash.find_next();
end;
end;
output;
end;
drop n _: rc;
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.