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;
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.
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;
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 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
/******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;)
Thank you, please create more interview videos
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;
yes, this can also be used.
Proc sql;
Select * from diwali_spends as x where 2=(select count(tran_amt) from diwali_spends as y where x.tran_amt
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.
sir is not workout when ever tran_amt having two same obs values ( ex = 1000 ,1000 ) ;
do u have sollution for u?
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;
Correct
very simple correct
None of them will work if data is same.
Does this also work if you want to find 3rd max value for each category?
Please share something on ETL with SAS
Will do so soon
Can we use "Largest" function from data step.
worth trying
Can't use rank function inside proc sql
you can try row number partition by, if you are working in SQL but not in proc sql for sure
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
Very nice
Thank you Sakshi
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
@@learnereaI got your point and thanks for the response, I will be going through the shared content!
/******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;)
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;
Rank,dense_rank,row_number wont work in sas.
Your expanation of the code is not clear.
You can share your doubts if any