topic Re: How to output just the last observation in a group following a condition? in SAS Programming

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

InêsMaximiano — Wed, 20 Sep 2023 08:25:50 GMT

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?

Thank you in advance!

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

LinusH — Wed, 20 Sep 2023 08:39:32 GMT

Untested:

data want;
   set have;
   if price < 0 then delete;
   by id year;
   if last.year then output;
   keep id year price;
run;

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

HarrySnart — Wed, 20 Sep 2023 08:54:04 GMT

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;

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

yabwon — Wed, 20 Sep 2023 09:04:36 GMT

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

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

Ksharp — Wed, 20 Sep 2023 11:48:35 GMT

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?

mkeintz — Wed, 20 Sep 2023 12:18:26 GMT

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.