Advance SAS Interview Question - Find the second max value for each of the category using PROC SQL

[@ajaykushwaha4233]

Thank you, please create more interview videos

[@SuperMrGilles]

Does this also work if you want to find 3rd max value for each category?

[@vinitvalvi]

Other than Proc SQL we can use First. for Ex :
proc sort data=LA.DIWALI_SPENDS;
by city descending tran_amt;
run;
Data Final;
set LA.DIWALI_SPENDS;
by city descending tran_amt;
if first.city then n=1;
else n+1;
run;
Data Final1;
set Final;
where n=2;
run;

[@learnerea]

yes, this can also be used.

[@akshayaseetharaman5687]

Can we use
Proc sql;
Create table old as
Select a.city,
Transamnt
From spend A
Where transamnt not in (select
City
,max(transamnt) as transamnt)
From spends
Group by 1);
Quit;
Proc sql;
Create table new as
Select a.city,
, max(transamnt)
From old A
Group by 1;
Quit;

[@learnerea]

lets understand this -
for example rs 10 was the maximum amount for delhi but second maxium for noida.. considering this query it will remove from noida as well when you use the exclusion logic not in.
to understand better, what I mean, use the file -
github.com/LEARNEREA/Excel_Files/blob/master/diwali_spends_manipulated.xlsx
and run both of the codes.. i.e. the one you see in the video and the one you have created and see the difference

[@Shradha_K]

Can we use "Largest" function from data step.

[@learnerea]

worth trying

[@nayakbibhuti085]

Can't use rank function inside proc sql

[@learnerea]

you can try row number partition by, if you are working in SQL but not in proc sql for sure

[@Milind12000]

Proc sql;
Select * from diwali_spends as x where 2=(select count(tran_amt) from diwali_spends as y where x.tran_amt

[@KIRITO-xo6qh]

Hello @Milind Patil
I ran your code and from my understanding it can be used for finding Nth value not only for 2 highest.
But I find it a little difficult to understand it so if you can explain it it will be a huge help.
Thank you.

[@ankababupodili]

sir is not workout when ever tran_amt having two same obs values ( ex = 1000 ,1000 ) ;
do u have sollution for u?

[@RatneshKumar-bc1jg]

Please share something on ETL with SAS

[@learnerea]

Will do so soon

[@parvsharma8767]

proc sql;
select city, max(sale) from sales where sale not in (
select max(sale) from sales group by city)
group by city;
quit;
sir I used this and got the correct output, is this also valid?

[@learnerea]

Good point, for this purpose only I created this video with inner join. let me try to explain you -
for example rs 10 was the maximum amount for delhi but second maxium for noida.. considering this query it will remove from noida as well when you use the exclusion logic not in.
to understand better, what I mean, use the file -
github.com/LEARNEREA/Excel_Files/blob/master/diwali_spends_manipulated.xlsx
and run both of the codes.. i.e. the one you see in the video and the one you have created and see the difference

[@parvsharma8767]

@@learnereaI got your point and thanks for the response, I will be going through the shared content!

[@prashanthdeva2770]

Proc sort data=second max value ; out= sorteddata;
by descending trnsamnt:
Proc print data=sorteddata (firstobs=2 );run;
Or
Data secondvalue;
Set sorteddata;
If _N_=2 then output;
Run;

[@sudheerupputuri932]

Correct

[@nipunshetty9640]

very simple correct

[@ajaykushwaha4233]

None of them will work if data is same.

[@vaishnavi4639]

but we want 2nd highest as of each city

[@vaishnavi4639]

this is just giving the 2nd highest and not as of city

[@sakshiverma9058]

Very nice

[@learnerea]

Thank you Sakshi

[@Anj1907]

/******For finding out the 1st ,2nd ,3rd and so on maximum value*****/
%Macro __findout(value=);
proc sort data = sashelp.class out=class(where=(~missing(height)));
by descending Height;
run;
Data class1;
Set class;
by descending height ;
m+1;
if m = "&value." ;
run;
%Mend;
%__findout(value=2);
If you want to know the first maximum value ,just modify the value as 1 .
Likewise ,for checking the maximum value by group , just add by variable in by statement and also use first.variable(if first.variable then m =1 else m+1;)

[@Lavin13]

Your expanation of the code is not clear.

[@learnerea]

You can share your doubts if any

[@teddygamingytff6739]

Proc sql;
Select * from
(
SELECT city, tranamt dense_rank ( ) over (partition by city order by tranamt desc) r from table_name) where r=2;
Quit;

[@solmanraj3140]

Rank,dense_rank,row_number wont work in sas.