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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.