working within the observation with the same id.

[willy0625]

Dear SAS users.

I am currently working with the attatched dataset called "test".

where the good and bad variables are binary.

I want to keep ONLY the groups that

- have a good flag in the first row

- have a bad flag in any row after the first row.

Also assign a string as a fourth variable that would have "Y" if they satify the above crieteria or "N" otherwise.

Many thanks.

[Ksharp]

You don't post ouput you want to see. So

Assuming only good or bad can be 1.(i.e. good=1 bad=0   or   good=0  bad=1).

libname x v9 'c:\';
proc means data=x.test nway noprint;
 class id;
 var bad;
 output out=temp(drop=_type_ rename=(id=_id))  sum= /autoname;
run;
data want(drop=_: bad_sum);
 set x.test;
 retain flag ' ';
 if id ne lag(id) then do;
   flag='N';
   if good=1 then do;
   do i=1 to _nobs;
    set temp nobs=_nobs point=i;
    if id=_id and _freq_=bad_sum+1 and _freq_ ne 1 then do; flag='Y'; leave;end;
   end;
   end;
 end;
run;

Ksharp

[Ksharp · Accepted solution]

data want(drop=one two);
 one=1; two=1;
 do until(last.id);
  set test;
  by id notsorted; 
  if first.id and good ne 1 then one=0;
  if (not first.id and bad ne 1) or (first.id and last.id) then two=0;
 end;

 if one and two then flag='Y';
   else flag='N';

 do until(last.id);
  set test;
  by id notsorted; 
 output;
 end;
run;

Ksharp