DATA Step, Macro, Functions and more

selecting last group of observation from a data set

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

selecting last group of observation from a data set

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


Accepted Solutions
Solution
‎03-16-2017 10:36 AM
Super User
Posts: 11,343

Re: selecting last group of observation from a data set

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


All Replies
Solution
‎03-16-2017 10:36 AM
Super User
Posts: 11,343

Re: selecting last group of observation from a data set

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. 

Super User
Posts: 7,782

Re: selecting last group of observation from a data set

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...

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,955

Re: selecting last group of observation from a data set

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.

Regular Contributor
Posts: 233

Re: selecting last group of observation from a data set

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;
PROC Star
Posts: 7,474

Re: selecting last group of observation from a data set

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

 

Super User
Posts: 10,028

Re: selecting last group of observation from a data set


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;


☑ This topic is solved.

Need further help from the community? Please ask a new question.

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