Dear all,
For the following sample dataset I need to retrieve the last value of DATE and TIME within each group (variableID).
data have;
ID | DATE | TIME |
50001 | 01/02/2018 | 112132 |
50001 | 12/12/2018 | 141625 |
50001 | 22/09/2018 | 204958 |
50001 | 02/05/2018 | 115545 |
50002 | 14/08/2018 | 110038 |
50002 | 27/11/2018 | 125737 |
50002 | 23/06/2018 | 105847 |
50002 | 22/12/2018 | 111211 |
50003 | 12/08/2018 | 120045 |
50003 | 14/08/2018 | 105922 |
50003 | 11/09/2018 | 103040 |
50003 | 13/08/2018 | 141713 |
run;;
Therefore the output shall be:
ID | DATE | TIME |
50001 | 22/09/2018 | 204958 |
50001 | 12/12/2018 | 141625 |
50002 | 27/11/2018 | 125737 |
50002 | 22/12/2018 | 111211 |
50003 | 14/08/2018 | 105922 |
50003 | 11/09/2018 | 103040 |
I have the following code but it only shows me the last one,
data want;
if 0 then set have;
call missing(dt);
do until(last.id);
set have;
by id;
dt=dt<>dhms(date,0,0,time);
end;
drop dt;
run;
Thank you all.
UNTESTED CODE
data have2;
set have;
by id;
if first.id then count=0;
count+1;
run;
proc sql;
create table want as select * from have2
where count>=max(count)-1
group by id;
quit;
If you want the last observation, you will need to sort your data. Easiest for your purposes:
proc sort data=have;
by id descending date descending time;
run;
Then you can select the first two observations for each ID:
data want;
set have;
by id;
if first.id then counter=1;
else counter + 1;
if counter <= 2;
drop counter;
run;
What did you intend dt to be? You create this variable (BTW, it will always end up zero), but do not do anything with it besides dropping it.
If you want the last two observations within a group, sort in reverse order, set a counter to 1 at first., and counter + 1 else, and use a subsetting if for counter <= 2.
proc sort data=have;
by id descending date descending time;
run;
data want;
set have;
by id;
if first.id
then counter = 1;
else counter + 1;
if counter <= 2;
drop counter;
run;
proc sort data=want;
by id date time;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.