Hi,
I have a dataset called data_1, I would like to delete smallest on largest 'Room_Rate' from the data within each (Brand, HTL_CD, Prod_Class_Name, RM_NBR, RM_TYP, REF_ROOM_TYP, SEASON_ID, DY_TYP) combination.
For the example as below (data_1), I would like to delete the first and last row.
If I used the following syntax:
Proc Sql;
Create Table data_2 as select
Brand
,Htl_Cd
,Prod_Class_Name
,RM_TYP
,REF_RM_TYP
,Season_Id
,DY_TYP
,Room_Rate
from data_1
group by 1, 2, 3, 4, 5, 6, 7
having Room_Rate > min(Room_Rate) and Room_Rate < max(Room_Rate)
order by 1, 2, 3, 4, 5, 6, 7;
Quit;
First two rows are deleted as they both have 'Room_Rate' $93. as well as the last row with 'Room_Rate' $144.
But I do would like to keep one of the $93 row.
Is there any way to handle it?
Thanks!
If you have sorted by room_rate, then just delete the first and last observation
data want;
set have end=eof;
if _n_>1 and not eof then output;
run;
Thanks @PaigeMiller
In this way, first observation and last observation could be removed.
However, it does not apply to each group, (other Season_Id, DY_TYP, ...)
You have NOT shown us data with groups. We can't read your mind. We can't provide a solution for data you didn't give us.
Show us data with groups ... or simply change the solution I gave you to include FIRST. and LAST. logic, and the solution I gave works fine.
Hi @PaigeMiller
I modified it to :
Data data_2;
Set data_1;
by Brand
Htl_Cd
Prod_Class_Name
RM_TYP
REF_RM_TYP
Season_Id
DY_TYP;
if not
first.Room_Rate
and not
last.Room_Rate
then output;
Run;
It didn't delete any observation within each group. Anything wrong with it?
Thanks!
use
FIRST.DY_TYP
and
LAST.DY_TYP
This is awesome, thanks!
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.