BookmarkSubscribeRSS Feed
InêsMaximiano
Fluorite | Level 6

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

 

IDYEARMONTHPRICE
A2020Janeiro300
A2021Janeiro0
A2022Janeiro0
B2021Janeiro300
B2022Abril450
B2022Maio400
B2022Junho0
B2022Julho0

 

 

Does anyone know how to output it like this:

IDYEARMONTHPRICE
A2020Janeiro300
A2021Janeiro0
A2022Janeiro0
B2021Janeiro300
B2022Abril450
B2022Maio400
B2022Junho400
B2022Julho400

 

or just:

IDYEARPRICE
A2020300
A20210
A20220
B2021300
B2022400

 

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

 

Thank you in advance!

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
HarrySnart
SAS Super FREQ

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;

HarrySnart_0-1695200037185.png

 

 

yabwon
Meteorite | Level 14

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



Ksharp
Super User
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;
mkeintz
Jade | Level 19

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

--------------------------

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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