selecting last group of observation from a data set

[John1231]

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

[ballardw · Accepted solution]

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]

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]

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]

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]

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]

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;