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

hi,

 

suppose i have a data set

 

n

year

 

1

20170314

 

2

20170314

 

3

20170315

 

4

20170315

 

5

20170315

 

6

20170316

 

7

20170316

 

8

20170316

 

 

and I need to select only the last group of 20170316. I cannot specify "where 20170316" because it's dynamic and it can change. I need an universal code that will select always last group of observations in a dataset.

 

thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

One way

proc sql;
   create table want as
   select * 
   from dataset
   having variable = max(variable);
quit;

Where dataset is your data set name and variable is the name of the variable of interest.

 

There will be a note like this in the log:

NOTE: The query requires remerging summary statistics back with the original data.

because of using the max in this way. 

View solution in original post

6 REPLIES 6
ballardw
Super User

One way

proc sql;
   create table want as
   select * 
   from dataset
   having variable = max(variable);
quit;

Where dataset is your data set name and variable is the name of the variable of interest.

 

There will be a note like this in the log:

NOTE: The query requires remerging summary statistics back with the original data.

because of using the max in this way. 

Kurt_Bremser
Super User
data have;
input n year :yymmdd8.;
format year yymmddn8.;
cards;
1 20170314
2 20170314
3 20170315
4 20170315
5 20170315
6 20170316
7 20170316
8 20170316
;
run;

proc sql;
create table want as
select * from have
having year = max(year);
quit;

Note how I presented example data in a data step. This enables everyone to recreate the dataset with a simple copy/paste and run, and is the way how example data should be posted for convenience. A macro to convert any dataset to a data step can be found at https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

SAS can do this automatically with by groups:

data want;
  set have;
  by year;
  if last.year then output;
run;

When you do by <variables> in a datastep like this, first. and last. flags are put in for each by group, note that it is a heirachy, so:

by vara varb;

Will put a first and last for varb within vara which will also get a first and last for each group.

gamotte
Rhodochrosite | Level 12

If the last group has not the maximum value :

 

data _NULL_;
	if 0 then set have nobs=numobs;
	set have point=numobs;
	call symput("lastgroupyear",year);
	stop;
run;

data want;
	set have;
	where year=&lastgroupyear;
run;
art297
Opal | Level 21
data want ;
  do until (eof1);
    set have end=eof1;
    _n_=max(_n_,year);
  end;
  do until (eof2);
    set have end=eof2;
    if year eq _n_ then output;
  end;
run;

Art, CEO, AnalystFinder.com

 

Ksharp
Super User

data have;
input n year :yymmdd8.;
format year yymmddn8.;
cards;
1 20170314
2 20170314
3 20170315
4 20170315
5 20170315
6 20170316
7 20170316
8 20170316
;
run;


data want;
 do until(last.year);
  set have nobs=nobs;
  by year notsorted;
  count+1;
 end;
 
 do until(last.year);
  set have;
  by year notsorted;
  if count=nobs then output;
 end;
 drop count;
run;


hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 3481 views
  • 3 likes
  • 7 in conversation