BookmarkSubscribeRSS Feed
BrahmanandaRao
Lapis Lazuli | Level 10
proc import datafile = "C:\Users\xxxx\Desktop\US_Sales.xlsx"
out=US_Sales
dbms=xlsx;
run;


proc sort data = US_Sales;
by descending  Sales  ;
run;

data Second_Max (Keep=Country  City State Region Sales);
set US_Sales;
by    descending   Sales;
if first.Sales then row = 0;
row + 1;
if row = 2 ;		proc print;
run;


proc means data=US_Sales;
class State Region /descending;
var Sales ;
output out=grouped sum=;
run;

How to  find Second max Sales for each state wise 

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

See if you can use this as a template.

 

proc sort data = sashelp.class out = class;
   by sex height;
run;

data want(drop = c);
   set class;
   by sex;
   if first.sex then c = 0;
   c + 1;
   if c = 2;
run;
BrahmanandaRao
Lapis Lazuli | Level 10
proc sort data=US_Sales out=sales_data (keep = state, Sales);
	by  descending Sales ;
	run;

	data ex (keep=State ,Region, Sales);
	set sales_data;
	by descending Sales ;
	if first.Sales then row=0;
	row+1;
	if row=2;proc print;
	run;

Its not give correct output please correct

PeterClemmensen
Tourmaline | Level 20

However, if you're not interested in the entire observation, you could use the IDGROUP Option on Proc Summary like this

 

proc summary data=sashelp.class nway noprint;
   class sex;
   var height;
   output out=want(keep = sex height_2) idgroup (max(height) out[3] (height) =) / autolabel autoname;
run;
BrahmanandaRao
Lapis Lazuli | Level 10
proc sort data=US_Sales out=sales_data (keep = state Sales);
	by  State descending Sales ;
	run;

	data ex (keep=State Region Sales);
	set sales_data;
	by descending Sales ;
	if first.State then row=0;
	row+1;
	if row=2;proc print;
	run;

I want second max  sales for each state but it give in EX dataset only indiana  but it gives different output

 

i want below output

 

proc sql;
select State, max(Sales) as value
from (select * from US_Sales group by State having max(Sales) ne Sales)
/*where State in ('Arizona','New York')*/
group by State ;
quit;

Anandkvn_0-1617363181257.png

 

Kurt_Bremser
Super User

Read the log, and fix the problems:

 88         data ex (keep=State Region Sales);
 89           set sales_data;
 90           by descending Sales ;
 91           if first.State then row=0;
 92           row+1;
 93           if row=2;
 94         
 95         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 WARNING: The variable Region in the DROP, KEEP, or RENAME list has never been referenced.
 ERROR: BY variables are not properly sorted on data set WORK.SALES_DATA.
 State=Alabama Sales=3.62 FIRST.Sales=1 LAST.Sales=1 first.State=0 row=60 _ERROR_=1 _N_=61

Shanghai-sized hint: the BY statement in the sort and the data steps need to be the same.

And you missed to KEEP Region somewhere.

Ksharp
Super User

Also try proc ranks.

 

proc sort data = sashelp.class out = class;
   by sex height;
run;
proc rank data = class out = want descending ties=dense;
   by sex ;
 var height;
 ranks rank_height;
run;
PeterClemmensen
Tourmaline | Level 20

Just for fun

 

data want;
   dcl hash hoh(ordered : "Y");
   hoh.definekey("sex");
   hoh.definedata("h", "hi", "sex");
   hoh.definedone();
   dcl hiter i("hoh");
 
   do until(z);
      set sashelp.class end = z;
      if hoh.find() ne 0 then do;
         dcl hash h(dataset : "sashelp.class(obs=0)", multidata : "Y", ordered : "D");
         h.definekey("height");
         h.definedata(all : "Y");
         h.definedone();
         dcl hiter hi("h");
         hoh.add();
      end;
      h.add();
   end;
 
   do while(i.next() = 0);
      hi.next();
      hi.next();
      output;
   end;
 
run;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2565 views
  • 3 likes
  • 4 in conversation