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!
Untested:
data want;
set have;
if price < 0 then delete;
by id year;
if last.year then output;
keep id year price;
run;
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;
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
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;
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.
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.