Does this also work if you want to find 3rd max value for each category?
@vinitvalvi
10 ай бұрын
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
10 ай бұрын
yes, this can also be used.
@ajaykushwaha4233
2 жыл бұрын
Thank you, please create more interview videos
@akshayaseetharaman5687
11 ай бұрын
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
11 ай бұрын
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
@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.
@Milind12000
2 жыл бұрын
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?
@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
11 ай бұрын
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
11 ай бұрын
@@learnereaI got your point and thanks for the response, I will be going through the shared content!
@Shradha_K
9 ай бұрын
Can we use "Largest" function from data step.
@learnerea
9 ай бұрын
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
@RatneshKumar-bc1jg
2 жыл бұрын
Please share something on ETL with SAS
@learnerea
2 жыл бұрын
Will do so soon
@Lavin13
8 ай бұрын
Your expanation of the code is not clear.
@learnerea
7 ай бұрын
You can share your doubts if any
@sakshiverma9058
2 жыл бұрын
Very nice
@learnerea
2 жыл бұрын
Thank you Sakshi
@Anj1907
8 ай бұрын
/******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;)
@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;
Пікірлер: 29