topic Re: Pulling back most recent result per another field in SAS Programming

Pulling back most recent result per another field

Cheesiepoof05 — Thu, 10 Aug 2023 17:23:28 GMT

I have the following code and table I'm using. I am trying to do two different things.

1. Pull back just the rows with the most recent date per product. IE there would be three rows, one for each fruit, and the most recent date per fruit.

2. Do the same thing as 1, except I want to add code that allows for a prompt where I will enter a certain date and just pull back the most recent record per fruit less than or equal to the date I entered.

PROC SQL;

CREATE TABLE WORK.Most_Recent_Price_Per_Fruit AS

SELECT

Product,

Date,

Price

FROM Database.FruitTable

AND Price NOT= 0

ORDER BY Product, Date

;

QUIT;

Date	Product	Price
01Jan2019	Oranges	$1.00
01Jun2020	Oranges	$1.75
01Mar2023	Oranges	$1.50
01Sep2021	Apples	$1.00
01Mar2023	Apples	$2.00
01Jan2019	Bananas	$5.00
01Jun2020	Bananas	$3.00

Results from option 1 in blue (rest would not be pulled back just included for reference)
Date	Product	Price
01Jan2019	Oranges	$1.00
01Jun2020	Oranges	$1.75
01Mar2023	Oranges	$1.50
01Sep2021	Apples	$1.00
01Mar2023	Apples	$2.00
01Jan2019	Bananas	$5.00
01Jun2020	Bananas	$3.00

Results from option 2 in blue assuming entering prompt date of 01MAY2022 (rest would not be pulled back just included for reference)
Date	Product	Price
01Jan2019	Oranges	$1.00
01Jun2020	Oranges	$1.75
01Mar2023	Oranges	$1.50
01Sep2021	Apples	$1.00
01Mar2023	Apples	$2.00
01Jan2019	Bananas	$5.00
01Jun2020	Bananas	$3.00

Re: Pulling back most recent result per another field

PaigeMiller — Thu, 10 Aug 2023 18:20:13 GMT

Part 1

proc sort data=have;
    by product date;
run;
data want;
    set have;
    by product;
    if last.product;
run;

Part 2

proc sort data=have(where=(date<'01MAY2022'd)) out=have1;
    by product date;
run;
data want;
    set have1;
    by product;
    if last.product;
run;

Re: Pulling back most recent result per another field

Cheesiepoof05 — Thu, 10 Aug 2023 18:46:13 GMT

Thank you, sir. Pardon my rookie question, but where does that go within my code?

Re: Pulling back most recent result per another field

PaigeMiller — Thu, 10 Aug 2023 18:52:06 GMT

It replaces your SQL code.

Re: Pulling back most recent result per another field

Cheesiepoof05 — Thu, 10 Aug 2023 18:56:55 GMT

Again sorry but I'm very new at this. If I completely replace my code with what you sent, how would it know what table to hit against and limits and such?

Re: Pulling back most recent result per another field

Tom — Thu, 10 Aug 2023 20:00:23 GMT

@Cheesiepoof05 wrote:

Again sorry but I'm very new at this. If I completely replace my code with what you sent, how would it know what table to hit against and limits and such?

Replace the name HAVE with the name of your actual input dataset.

Replace the name WANT with the name of your actual output dataset.

Re: Pulling back most recent result per another field

Cheesiepoof05 — Fri, 11 Aug 2023 14:40:00 GMT

Thank you, Tom! I really appreciate you walking me through I'm sure what was simple to you as I build up my knowledge. I didn't recognize those as variables but makes perfect sense now. I got it working after that. I appreciate your guidance.