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.
proc summary data=have;
class item_id;
var price;
output out=want min()=;
run;
Or
proc sql;
create table want as
select
item_id,
min(price)
from have
group by item_id
;
quit;
@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-dat... 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.
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);
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.