Fluorite | Level 6

## How to output just the last observation in a group following a condition?

Hi all, here's the result I got from SAS:

 ID YEAR MONTH PRICE A 2020 Janeiro 300 A 2021 Janeiro 0 A 2022 Janeiro 0 B 2021 Janeiro 300 B 2022 Abril 450 B 2022 Maio 400 B 2022 Junho 0 B 2022 Julho 0

Does anyone know how to output it like this:

 ID YEAR MONTH PRICE A 2020 Janeiro 300 A 2021 Janeiro 0 A 2022 Janeiro 0 B 2021 Janeiro 300 B 2022 Abril 450 B 2022 Maio 400 B 2022 Junho 400 B 2022 Julho 400

or just:

 ID YEAR PRICE A 2020 300 A 2021 0 A 2022 0 B 2021 300 B 2022 400

So I can obtain the last positive (>0) price for each year and ID?

5 REPLIES 5
Tourmaline | Level 20

## Re: How to output just the last observation in a group following a condition?

Untested:

``````data want;
set have;
if price < 0 then delete;
by id year;
if last.year then output;
keep id year price;
run;``````
Data never sleeps
SAS Super FREQ

## Re: How to output just the last observation in a group following a condition?

Hi, is this the logic you're after?

``````/* load data */
data have;
input ID \$	YEAR	MONTH \$	PRICE;
datalines;
A 2020 Janeiro 300
A 2021 Janeiro 0
A 2022 Janeiro 0
B 2021 Janeiro 300
B 2022 Abril 450
B 2022 Maio 400
B 2022 Junho 0
B 2022 Julho 0
;

/* assign month (quick workaround) */
data have;
set have;
if month = 'Janeiro' then mth=1;
if month='Abril' then mth=4;
if month = 'Maio' then mth=5;
if month = 'Junho' then mth=6;
if month = 'Julho' then mth=7;
run;

/* get last postive amount by year */
proc sql; create table filter as select id, year, max(mth) as mth from (select * from have where price gt 0) group  by id, year; quit;

/* filter by join */
proc sql;create table want as
select a.* from have as a inner join filter as b on a.id=b.id and a.year=b.year and a.mth=b.mth;quit;``````

Meteorite | Level 14

## Re: How to output just the last observation in a group following a condition?

Try this:

``````
data have;
input ID \$	YEAR	MONTH \$	PRICE;
cards;
A	2020	Janeiro	300
A	2021	Janeiro	0
A	2022	Janeiro	0
B	2021	Janeiro	300
B	2022	Abril	450
B	2022	Maio	400
B	2022	Junho	0
B	2022	Julho	0
;
run;
proc print;
run;

data want;
merge have have(rename=(PRICE=P) where=(P>0));
by ID	YEAR;
PRICE = max(PRICE,P);
if last.year;
drop P MONTH;
run;
proc print;
run;
``````

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Super User

## Re: How to output just the last observation in a group following a condition?

``````data have;
input ID \$	YEAR	MONTH \$	PRICE;
datalines;
A 2020 Janeiro 300
A 2021 Janeiro 0
A 2022 Janeiro 0
B 2021 Janeiro 300
B 2022 Abril 450
B 2022 Maio 400
B 2022 Junho 0
B 2022 Julho 0
;
data want;
do until(last.year);
set have;
by id year;
if PRICE>0 then want=PRICE;
end;
want=coalesce(want,PRICE);
keep id year want;
run;``````

## Re: How to output just the last observation in a group following a condition?

If you want the multiple-obs-per-year output, then:

``````data have;
input ID \$	YEAR	MONTH \$	PRICE;
datalines;
A 2020 Janeiro 300
A 2021 Janeiro 0
A 2022 Janeiro 0
B 2021 Janeiro 300
B 2022 Abril 450
B 2022 Maio 400
B 2022 Junho 0
B 2022 Julho 0
run;

data want (drop=_:);
set have (rename=(price=_orig_price));
by id year;
retain price;
price=ifn(_orig_price>0 or first.year,_orig_price,price);
*if last.year;
run;``````

If you really want just one obs per year, then de-comment the subsetting IF statement.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 5 replies
• 238 views
• 1 like
• 6 in conversation