Conditionally extract a single obs

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Conditionally extract a single obs

[ Edited ]

Hi,

I have a data like below:

1.png

 

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! 


Accepted Solutions
Solution
‎12-13-2017 08:21 AM
Super User
Posts: 5,972

Re: Conditionally extract a single obs

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.

View solution in original post


All Replies
Super User
Super User
Posts: 8,634

Re: Conditionally extract a single obs

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;
PROC Star
Posts: 1,070

Re: Conditionally extract a single obs

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;
Solution
‎12-13-2017 08:21 AM
Super User
Posts: 5,972

Re: Conditionally extract a single obs

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 112 views
  • 1 like
  • 4 in conversation