How to output just the last observation in a group

[ywhui9]

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!

[PaigeMiller · Accepted solution]

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

[ywhui9]

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

[kiranv_]

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);

[ywhui9]

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!

[PeterClemmensen]

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;

[PaigeMiller]

---

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

[Astounding]

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?

[Astounding]

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?

[ywhui9]

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!

[FreelanceReinh]

---

@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:

1. Reading multiple records into one observation (by @blowsnow__)
2. How do i read these data correctly in SAS? (by @Makmatt)
3. How to create a SAS data set that contains only the last record of each object ? (by @Kitty28)
4. Creating variables from existing variables (by @blowsnow__ again)
5. How to output the last part (data programming) (by @kennyA)
6. Single observation for each variable (by @blowsnow__ again!)

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

What if your course instructors follow these threads?

[ywhui9]

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

[FreelanceReinh]

@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!

[novinosrin]

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!