BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Crubal
Quartz | Level 8

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.

 

 Data_1.PNG

 

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

use

 

FIRST.DY_TYP

 

and

 

LAST.DY_TYP

--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Crubal
Quartz | Level 8

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, ...)

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Crubal
Quartz | Level 8

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!

PaigeMiller
Diamond | Level 26

use

 

FIRST.DY_TYP

 

and

 

LAST.DY_TYP

--
Paige Miller
Crubal
Quartz | Level 8

This is awesome, thanks! 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 6 replies
  • 2465 views
  • 0 likes
  • 2 in conversation