BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Cheesiepoof05
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Cheesiepoof05
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

It replaces your SQL code.

--
Paige Miller
Cheesiepoof05
Obsidian | Level 7

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?

Tom
Super User Tom
Super User

@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.

Cheesiepoof05
Obsidian | Level 7

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 916 views
  • 2 likes
  • 3 in conversation