BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cczzzl
Calcite | Level 5

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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