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!
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.
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;
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;
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.
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.