topic last observation and frequency table in SAS Programming

last observation and frequency table

keldel — Wed, 04 Aug 2021 20:09:52 GMT

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!

Re: last observation and frequency table

jimbarbour — Wed, 04 Aug 2021 20:21:07 GMT

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

Re: last observation and frequency table

keldel — Wed, 04 Aug 2021 20:26:35 GMT

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

Thank you for your time and guidance.

Re: last observation and frequency table

jimbarbour — Wed, 04 Aug 2021 20:28:05 GMT

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

Jim