05-07-2018 01:00 PM
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?
05-07-2018 01:13 PM
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;
05-07-2018 01:29 PM
In this way, first observation and last observation could be removed.
However, it does not apply to each group, (other Season_Id, DY_TYP, ...)
05-07-2018 01:30 PM - edited 05-07-2018 01:31 PM
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.
05-07-2018 03:45 PM
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?
Need further help from the community? Please ask a new question.