topic Re: Second max Sales for each state in SAS Programming

Second max Sales for each state

BrahmanandaRao — Fri, 02 Apr 2021 05:27:54 GMT

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

Re: Second max Sales for each state

PeterClemmensen — Fri, 02 Apr 2021 05:33:57 GMT

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;

Re: Second max Sales for each state

PeterClemmensen — Fri, 02 Apr 2021 05:44:08 GMT

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;

Re: Second max Sales for each state

BrahmanandaRao — Fri, 02 Apr 2021 09:15:14 GMT

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

Re: Second max Sales for each state

Kurt_Bremser — Fri, 02 Apr 2021 10:00:30 GMT

Sort by state and descending sales, and start your counter variable at first.state.

Re: Second max Sales for each state

Ksharp — Fri, 02 Apr 2021 11:17:54 GMT

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;

Re: Second max Sales for each state

BrahmanandaRao — Fri, 02 Apr 2021 11:33:51 GMT

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;

Re: Second max Sales for each state

Kurt_Bremser — Sat, 03 Apr 2021 10:03:32 GMT

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.

Re: Second max Sales for each state

PeterClemmensen — Sun, 04 Apr 2021 19:31:25 GMT

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;