topic Re: Retaining only the lowest price per item number in SAS Programming

Retaining only the lowest price per item number

Cheesiepoof05 — Wed, 10 Jan 2024 14:16:54 GMT

Hello, I have a spreadsheet with many columns but want to limit it down based on two fields. I'm trying to only retain the lowest "Price" per "Item_ID". Some Item_IDs will have multiple records with the same lowest price so I don't care which of the records with lowest price it keeps, but just need one of them. Example below with the kept records in yellow.

Re: Retaining only the lowest price per item number

Kurt_Bremser — Wed, 10 Jan 2024 14:20:36 GMT

proc summary data=have;
class item_id;
var price;
output out=want min()=;
run;

proc sql;
create table want as
  select
    item_id,
    min(price)
  from have
  group by item_id
;
quit;

Re: Retaining only the lowest price per item number

ballardw — Wed, 10 Jan 2024 16:37:04 GMT

@Cheesiepoof05 wrote:

Hello, I have a spreadsheet with many columns but want to limit it down based on two fields. I'm trying to only retain the lowest "Price" per "Item_ID". Some Item_IDs will have multiple records with the same lowest price so I don't care which of the records with lowest price it keeps, but just need one of them. Example below with the kept records in yellow.

Example of your SAS data set. From you description of "many columns" one suspects that this is going to be a couple steps depending on what is in those columns.

Showing an example with two columns when your problem states many obfuscates details that will be needed to solve the complete problem.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Re: Retaining only the lowest price per item number

Tom — Wed, 10 Jan 2024 17:16:02 GMT

Looks like you sorted the data (And hopefully you have moved it out of a Spreadsheet and into an actual dataset) so a simple data step with BY group processing should work.

data want;
  set have;
  by item_id price;
  if first.item_id;
run;

If some of the prices are missing then also add a WHERE statement to ignore those observations since missing values are smaller than any actual value.

where not missing(price);