Hi all, I have a far more complex dataset but below is some sample data. Basically I am needing to iterate through the list of ID's and determine the FIRST instance of ACTION = 2. I need all data after this first instance BY ID to be retained and anything before that deleted. Below is what I've tried any pointers on where I'm going wrong?
Note* My real data is already sorted and the dates actually appear, I don't believe in this instance they hold any relevance but you'll realize they do not populate correctly.
DATA HAVE;
INPUT ID DATE ACTION;
DATALINES;
1 JAN012019 1
1 FEB012019 1
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;RUN;
DATA WANT;
INPUT ID DATE ACTION;
DATALINES;
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;
RUN;
DATA WANT;
SET HAVE;
DO UNTIL (ID = ID AND ACTION = 2);
DELETE;
END;
RUN;
DATA HAVE;
INPUT ID DATE :$10. ACTION;
DATALINES;
1 JAN012019 1
1 FEB012019 1
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;RUN;
data want;
do _n_=0 by 0 until(last.id);
set have;
by id;
if ACTION=2 then _n_=1;
if _n_ then output;
end;
run;
/*Or*/
data want;
do until(last.id);
set have;
by id;
_iorc_+ ACTION=2;
if _iorc_ then output;
end;
_iorc_=0;
run;
DATA HAVE;
INPUT ID DATE :$10. ACTION;
DATALINES;
1 JAN012019 1
1 FEB012019 1
1 MAR012019 2
2 JAN012019 2
2 FEB012019 1
;RUN;
data want;
do _n_=0 by 0 until(last.id);
set have;
by id;
if ACTION=2 then _n_=1;
if _n_ then output;
end;
run;
/*Or*/
data want;
do until(last.id);
set have;
by id;
_iorc_+ ACTION=2;
if _iorc_ then output;
end;
_iorc_=0;
run;
Thank you, I've only tested this on ~10 records of my live data but it seems to be holding up so far.
data have ;
input id date: $9. action ;
cards ;
1 jan012019 1
1 feb012019 1
1 mar012019 2
2 jan012019 2
2 feb012019 1
;
run ;
data want ;
do until (last.id) ;
set have ;
by id ;
if action = 2 then _n_ = 0 ;
if _n_ = 0 then output ;
end ;
run ;
Kind regards
Paul D.
Hi @Krueger One more fun before I catch the bus to get home, If your dates are numeric SAS dates and it looks like some fun to use them in proc sql 🙂
DATA HAVE;
INPUT ID DATE :date9. ACTION;
format date date9.;
DATALINES;
1 01JAN2019 1
1 01FEB2019 1
1 01MAR2019 2
2 01JAN2019 2
2 01FEB2019 1
;RUN;
proc sql;
create table want as
select *
from have
group by id
having date>=min(ifn(action=2,date,.))
order by id,date;
quit;
Have a good one and a great weekend. Ciao!
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.