proc sql exec;
connect to teradata as ter
(user=abc password=aa tdpid=DWPROD);
execute ( create volatile table peulot,
         no log as(  select       

           ir.branch_cust_ip ,
           case when Channel_Id in (1101,1102,1103,1105 ) then 1
           when Channel_Id in (1201 ,1203,1207,1208,1212,
           1220,1240,1401,1403,1499) then 2 
           when Channel_Id in (1301,1302,1304,1399) then 3
           when Channel_Id in (2501,1501,1601,1602) then 4 end 
           as code_channel,
           count(*) as counter
                       
from dwp_vall.V0500_1_FINANCIAL_EVENT V0500,   
     dwp_vall.V0X414_COST_TYPE_B as V0X414,
     dwp_sas.population ir    
where V0500.Event_Costing_Activity_Type_Co=V0X414.Cost_Activity_Type_Code
and V0500.branch_cust_ip=ir.branch_cust_ip   
and event_start_date ge 1070101
and event_start_date lt 1070901
group by 1,2    

with data on commit preserve rows)by ter;
 execute (commit work) by ter;
execute (

create volatile table chann_rank,
  no log as ( 
select   basis.branch_cust_ip,
           min( code_channel) as Channel_prior1,
           max( code_channel) as Channel_prior2 
       
from   ( select branch_cust_ip,
         code_channel,
         RANK(  ) OVER (PARTITION BY 
         branch_cust_ip ORDER by counter desc)  as Percentile_arutz
         from peulot
                        
         QUALIFY Percentile_arutz = 1  )  basis
        group by 1)

 with data on commit preserve rows)by ter;
 execute (commit work) by ter;

  create table m_irena as  
  select  * from connection to ter 
  (select * from chann_rank); 
  disconnect from ter;

quit;     
                      
)

QUALIFY an introduction to a conditional clause that, similar to HAVING, further filters rows from a WHERE clause. The major difference between QUALIFY and HAVING is that with QUALIFY the filtering is based on the result of performing various ordered analytical functions on the data.