topic Re: selecting last group of observation from a data set in SAS Programming

selecting last group of observation from a data set

John1231 — Thu, 16 Mar 2017 14:13:54 GMT

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

Re: selecting last group of observation from a data set

ballardw — Thu, 16 Mar 2017 14:27:08 GMT

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.

Re: selecting last group of observation from a data set

Kurt_Bremser — Thu, 16 Mar 2017 14:29:52 GMT

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-data-AKA-generate/ta-p/258712

Re: selecting last group of observation from a data set

RW9 — Thu, 16 Mar 2017 14:33:17 GMT

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.

Re: selecting last group of observation from a data set

gamotte — Thu, 16 Mar 2017 14:47:43 GMT

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;

Re: selecting last group of observation from a data set

art297 — Thu, 16 Mar 2017 14:49:41 GMT

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

Re: selecting last group of observation from a data set

Ksharp — Fri, 17 Mar 2017 04:07:20 GMT


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;