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;
/******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;)
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.
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;
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
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;
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?
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
