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
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.
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.
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...
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.
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.