BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
keldel
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
jimbarbour
Meteorite | Level 14

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

 

jimbarbour_0-1628108445773.png

 

View solution in original post

3 REPLIES 3
jimbarbour
Meteorite | Level 14

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

 

jimbarbour_0-1628108445773.png

 

keldel
Fluorite | Level 6

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

 

Thank you for your time and guidance. 

jimbarbour
Meteorite | Level 14

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

 

Jim

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 653 views
  • 1 like
  • 2 in conversation