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
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;
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
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;
Sort by state and descending sales, and start your counter variable at first.state.
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;
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.
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.