Conditionally extract a single obs

[cczzzl]

Hi,

I have a data like below:

I want to have a data with a single obs based on the last value of column C.

Specifically, if the last value is negative, then I want the fifth row, which is AXX  5 N or P;

and if it is positive, then I want the first row within positive value of C, which is AXX  2  1.

my code could only extract the last row in the case of negative value.

data data1; set data1  end = last;
by name;
if last then last_C = XXXXXX;
if last_c< 0 then do;
output;end;
run;

Any helps are very appreciated! 

[RW9]

Post test data in the form of a datastep, and use the code window (its the {i} above post area) to post the code.  As such this is untested:

data want;
  set have;
  by name;
  if last.name then do;
    if value < 0 then result=c;
    else do;
      if lag4(c) > 0 then result=lag4(c);
      else if lag3(c) > 0 then result=lag3(c);
      else if lag2(c) > 0 then result=lag2(c);
    end;
    output;
  end;
run;

[PeterClemmensen]

Something like this

data have;
input NAME$ B C;
datalines;
AXX 1 -1
AXX 2  1
AXX 3  2
AXX 4  3
AXX 5  4
;

proc sort data=have;
	by NAME descending C;
run;

data want;
	set have(where=(C>=0)) end=eof;
	by NAME;
	if first.NAME and C<0 then output;
	else if first.NAME and C>=0 then outflag=1;
	if eof and outflag=1 then output;
	retain outflag;drop outflag;
run;

[Astounding · Accepted solution]

Here's a way to retrieve the last value of C before you starting moving through all the observations:

data want;

if _n_=1 then set have (keep=C rename=(C=last_C)) nobs=_nobs_ point=_nobs_;

set have;

if last_C > 0 and C > 0 then do;

   output;

   last_C=.;

end;

else if last_C > . and _n_=_nobs_ then output;

drop last_C;

run;

It's untested code, for now.