topic Re: How to output just the last observation in a group in New SAS User

How to output just the last observation in a group

ywhui9 — Sun, 04 Nov 2018 12:45:46 GMT

Hi all, here's the result I got from SAS:

A 10

B 5

B 6

C 7

C 8

C 9

C 10

D 2

Does anyone know how to output it like this:

A 10

B 6

C 10

D 2

Thank you in advance!

Re: How to output just the last observation in a group

PaigeMiller — Sun, 04 Nov 2018 13:04:42 GMT

data want;
    set have;
    by id;
    if last.id;
run;

Re: How to output just the last observation in a group

ywhui9 — Sun, 04 Nov 2018 13:14:38 GMT

Thanks for your reply it works but can we make it without using proc sort and set statement?

Re: How to output just the last observation in a group

kiranv_ — Sun, 04 Nov 2018 13:50:37 GMT

but what is problem with sort and set statement. That is clean and easiest way to say. if you data is always in that order that your max id is last record then you can do

proc sql;
select * from have
group by id
having othercolumn = max(othercolumn);

Re: How to output just the last observation in a group

ywhui9 — Sun, 04 Nov 2018 13:53:01 GMT

set statement is totally fine and I understand it is the easiest way but I'm actually doing an assignment and we're told to solve this by using if-then, retain, output statement etc. Thanks tho!

Re: How to output just the last observation in a group

PeterClemmensen — Sun, 04 Nov 2018 14:25:58 GMT

I don't understand this? Do you want to use if-then, retain and an output statement?

If you want to avoid sorting and you know that the data is already grouped, you could do

data have;
input ID $ var;
datalines;
A 10
B 5
B 6
C 7
C 8
C 9
C 10
D 2
;

data want;
   set have;
   by ID notsorted;
   if last.ID;
run;

Re: How to output just the last observation in a group

Astounding — Sun, 04 Nov 2018 14:31:55 GMT

If you had to, you could load the data into a hash table (using methods that replace previously found values) and then unload the result into a data set. But why use a sledgehammer to kill a fly?

Re: How to output just the last observation in a group

Astounding — Sun, 04 Nov 2018 14:32:20 GMT

If you had to, you could load the data into a hash table (using methods that replace previously found values) and then unload the result into a data set. But why use a sledgehammer to kill a fly?

Re: How to output just the last observation in a group

PaigeMiller — Sun, 04 Nov 2018 14:37:59 GMT

@ywhui9 wrote:

Thanks for your reply it works but can we make it without using proc sort and set statement?

You have to explain further what you are doing, and WHY you don't want to use PROC SORT and the SET statement. The code is written a certain way for a reason (simplicity, directness); and without a good reason (which you have not specified), you are asking for a lot of unnecessary complications.

Re: How to output just the last observation in a group

ywhui9 — Sun, 04 Nov 2018 15:01:20 GMT

Sorry guys, I don't want to enclose too much detail (e.g. my codes) as I found some of my classmates are asking for the same question too. I know this looks stupid. I'm going to find out the solution myself. Thanks for all replies! Have a nice day!

Re: How to output just the last observation in a group

FreelanceReinh — Sun, 04 Nov 2018 15:26:13 GMT

@ywhui9 wrote:

I found some of my classmates are asking for the same question too. I know this looks stupid.

Indeed, it does.

Your presumed classmates have been posting essentially the same homework again and again during the past 48 hours:

Doesn't it defeat the purpose of homework to have experts do it?

What if your course instructors follow these threads?

Re: How to output just the last observation in a group

ywhui9 — Sun, 04 Nov 2018 15:56:15 GMT

@Reinhard That’s why I didn’t post any detail regarding the assignment. I put my code here in my last post because I had been working for many hours and I was almost done. It was about the timing of using an output statement and thanks some of you for pointing out the problem of my code. This time the same I didn’t want to ask for the answer directly but you think I did i’m sorry. I have no friends in this class so I have no idea about what they did before sorry for annoying you. I’ll ask the the same thing to my prof too if it is not weekend so indeed I don’t care whether my prof is following this post or not. Thanks for your reply tho.

Re: How to output just the last observation in a group

FreelanceReinh — Sun, 04 Nov 2018 16:55:38 GMT

@ywhui9: No problem, sorry, wasn't meant to be rude. Now I see, you just needed the last missing piece to the puzzle. That's fine. 🙂

@ywhui9 wrote: I have no friends in this class ...

Sad to read this. Luckily, this forum is full of nice people who spend a lot of their free time helping other members with their SAS (and statistics) questions. It's a great place for learning SAS (which is actually a never-ending task) because you can see how problems of all levels are tackled by experts with various backgrounds.

Good luck with your SAS course and happy learning!

Re: How to output just the last observation in a group

novinosrin — Sun, 04 Nov 2018 19:01:41 GMT

Hi @ywhui9 Interesting that a group of your classmates have found this amazing SAS family(our communities) while my mates are still pondering how to ask a clear question here. Plus, one of your questions(summary in one step that involved Look ahead) was something I personally enjoyed answering.

To your-" we're told to solve this by using if-then, retain, output statement etc" (supposing that's for extra credit?) add some flavor to astounding idea of hash replace the found previous values in this thread

data have;
input ID $ var;
datalines;
A 10
B 5
B 6
C 7
C 8
C 9
C 10
D 2
;

data _null_;
   dcl hash H (dataset:'have',ordered: "a",duplicate:'r') ;
   h.definekey  ("id") ;
   h.definedata ("id", "var") ;
   h.definedone () ;
h.output(dataset:'want');
stop;
retain _n_ _iorc_ ;
if 0 then do;set have;output;end;
run;

The above may make professors may think we are naughty but works for extra credit :), but I have always been that since childhood. Have fun!