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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 2445 views
  • 3 likes
  • 7 in conversation