last observation and frequency table

[keldel]

Hello,

I am attempting to take the last observation in each group of my dataset, and create a frequency table for a specific variable just using a subsetted dataset containing the last observation for each ID. 

Here is a sample from the dataset to help with visualization: 

ID AGE SMOKE

A 42 1

A 44 1

B 64 0

B 68 0

B 71 0

C 23 0

C 25 0

I have this code to take the last observation and another for the frequency table: 

proc sql;
select * from output_1
group by ID
having AGE=max(AGE);
quit;
proc freq data=output_1;
TABLES SMOKE;
quit;

My problem is the frequency table is using the whole dataset, not just the last observation. I want to pull the frequency for smoking (0 means not a smoker, 1 means yes smoker) from a dataset just using the last observation (oldest age) for ID A, B, and C. Can someone please provide guidance? Thank you!

[jimbarbour · Accepted solution]

Have you tried a Data step using LAST.ID?

Like the below.  The data must be in the correct order for NOTSORTED to work properly.  If the data is not already in the correct order, you would need to use Proc Sort and remove the NOTSORTED.  See results at bottom of post.

DATA	Have;
	INFILE	DATALINES;
	INPUT
	ID	$ AGE SMOKE;
DATALINES;
A 42 1
A 44 1
B 64 0
B 68 0
B 71 0
C 23 0
C 25 0
;
RUN;

DATA	Want;
	SET	Have;
		BY	ID	NOTSORTED;
	IF	LAST.ID;
RUN;

PROC	FREQ	DATA=Want;
	TABLES	Smoke;
RUN;

Jim

[keldel]

Wonderful! That worked. I hadn't been using the LAST. correctly when I tried that before. 

Thank you for your time and guidance. 

[jimbarbour]

You're welcome.  I'm glad it was what you needed.

Jim