BookmarkSubscribeRSS Feed
rajeshalwayswel
Pyrite | Level 9

data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;

 

In data steps and proc sql ?  

8 REPLIES 8
Reeza
Super User

proc sort data=have; by descending number;

run;

 

data want;

set have (obs=2 firstobs=2);

run;

 

Or see the SAS PROC UNIVARIATE/PROC MEANS on isolating the top X values or the Xth value. There's an example in the documentation for each of the procedures. 

SAS_inquisitive
Lapis Lazuli | Level 10
data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;

proc sql print;
    
   create table tmp1 as
	select name,number
	  from second
	order by number desc;

	create table tmp2 as
	   select name, number, monotonic() as rank
	    from tmp1
	    where calculated rank = 2;

	create table want as
	   select *
	     from tmp1 
		   where number in (select number from tmp2);
	 
quit;
subhroster
Fluorite | Level 6

 

proc transpose data= second out=trans_second;

run;

data max;

set trans_second;

max=largest(2,of col1-col7);

run;

 

Subhro Kar
www.9to5sas.com
ketan_korde
Fluorite | Level 6
Can you please explain what is happening in table want? Thank You.
mkeintz
PROC Star

With each incoming observation, you can just maintain a record of the 1st and 2nd maximum values and their corresponding observation numbers.  Then, at the end of the dataset, just reread the observation number (_p2 below) that corresponds to the 2nd largest value (_max2):

 

data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;

data want (drop=_:);
  set second end=eod;
  retain _max1 _max2  _p1 _p2 .;

  if number>_max1 then do;
    _max2=_max1;  _p2=_p1;
    _max1=number; _p1=_n_;
  end;
  else if number>_max2 then do;
    _max2=number; _p2=_n_;
  end;

  if eod ;
  set second point=_p2;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ChrisNZ
Tourmaline | Level 20


Like this?

 


proc sql outobs=1; 
  create table WANT1 as 
  select * 
  from HAVE 
  having NUMBER ne max(NUMBER) 
  order by NUMBER desc ;
quit; 

data WANT2;
  if 0 then set HAVE;
  dcl hash HT(dataset:'HAVE', ordered:'descending');
  HT.definekey('NUMBER');
  HT.definedata('NAME','NUMBER');
  HT.definedone();
  dcl hiter iter('HT');
  RC=iter.first();
  RC=iter.next();
run;

 

 

Patrick
Opal | Level 21

@rajeshalwayswel 

Or Proc Rank which lets you define how to deal with ties.

proc rank
    data=second
    ties=dense descending
    out=want(where=(number_rank=2))
    ;
  var number;
  ranks number_rank;
run;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 23236 views
  • 2 likes
  • 9 in conversation