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