%macro Rank(_dsetin, _dsetout, _ties, _Group, _MQVarIn1, _MQRankVar);
/**************************************************************************************/
/*  Name: RANK.sas                                  Auth: Phil Rack                   */
/*  Date: 5/16/2007                                  Revd:                            */
/*                                                                                    */
/*  Desc: Calculates a ranking for a specific variable.                               */
/*                                                                                    */
/*  Where: _dsetin    = the name of the dataset containing the variable to be ranked. */
/*         _dsetout   = the name of the new dataset that will contain the ranked var. */
/*         _ties      = the method of ranking used, LOW, HIGH, MEAN are options       */
/*         _Group     = 0,1 for no grouping, all other values group ranked variable.  */
/*                      4 for Quartiles, 10 for Deciles, and 100 for Percentiles.     */
/*         _MQVarIn1  = The variable to be ranked. Accepts only a single variable.    */
/*         _MQRankVar = the name of the new variable containing the rank value.       */
/*                                                                                    */
/*  Copyright (C) 2007 by MineQuest, LLC. All Rights Reserved. www.minequest.com      */
/**************************************************************************************/
   *--> Create local macro variables and initialize them to missing;
   %local _dsetnm dotpos libn xlen _Group _MQVarIn1 _dsetout _dsetin _MQRankVar _ties;
   
   %let _dsetnm=;
   %let dotpos=;
   %let libn=;
   %let xlen=;
   
   
  
   %let dotpos = %index(%str(%quote(&_dsetout)),.);
   %let xlen = %length(&_dsetout);
   %if "&dotpos" eq "0" %then %let Libn = work;
   %if "&dotpos" eq "0" %then %let _dsetnm = &_dsetout;
   
   %if "&dotpos" ne "0" %then %let Libn = %substr(&_dsetout,1,&dotpos-1);
   %if "&dotpos" ne "0" %then %let _dsetnm = %substr(&_dsetout,&dotpos + 1, &xLen-&dotpos);


/************************************************************************************/
/* If users wants TIES=LOW, begin calculating this option                           */
/************************************************************************************/

%If %UPCASE("&_ties") EQ "LOW" %Then %Do;

   *--> create an ID variable so we can get the data back into original form;
  data _MQtemp01_;
    set &_dsetin;
        _mqRecordId_ + 1;
  run;

  proc sort data= _MQtemp01_;
    by descending &_MQVarIn1;
  run;

  *--> Calculate a simple rank _ties=low;
  data _MQTemp02_;
    set _MqTemp01_; 
      Retain _CurrentCount_ 0;
      by descending &_MQVarIn1;
      _CurrentCount_ + 1;

    if first.&_mqVarin1 and &_MQVarIn1 ne lag(&_MQVarin1) then do;
      &_MQRankVar + 1;
      _CurrentRank_ = 0;
    end;  

    if first.&_MQVarIn1 and &_MQVarin1 eq lag(&_MQVarin1) then do;
      &_MQRankVar + 1;
      _currentRank_ = _CurrentCount_;
    end;

    if (_currentcount_ eq &_MQRankVar ) and _currentrank_ = 0 then &_MQRankVar + 0;
    if (_currentcount_ ne &_MQRankVar ) and _CurrentRank_ = 0 then &_MQRankVar = _CurrentCount_;

  Drop _CurrentCount_ _CurrentRank_ ;
run;

/************************************************************************************/
/* If GROUPS option was specified, i.e. value > 1 then start calculating groups     */
/************************************************************************************/

%If ("&_group" ne "0")  and ("&_group" ne "1") %Then %Do;
*--> get the overall N;
  Proc Means data= _mqTemp02_  noprint;
    var &_mqRankVar;
    output out=_mqtemp03_  n=_nall_;
  run;

  data _null_;
  set _mqTemp03_;
    call symput('nAll',_nall_);
  run;

  
  Proc delete data=_mqTemp03_; 
  run;

  proc sort data=_mqtemp02_;  
    by &_MqRankVar ; 
  run;

  data _MqTempFinal_;
  set _mqTemp02_;
     %If ("&_group" ne "0") or ("&_group" ne "1") %Then %Do;
          _MqGroupRank_ = FLOOR( &_MqRankVar * &_Group /( &nall  + 1 ) );
          Drop &_MqRankVar;
      %End;
  run;
  
  Proc datasets nolist ddname= work;
    modify _mqTempFinal_ ;
    rename _mqGroupRank_ = &_mqRankVar;
    quit;
  Run;
 
%End;

%IF ("&_group" eq "0")  or ("&_group" eq "1") %then %do;
  *--> Sort the data back into its original form;
  proc sort data=_MQTemp02_ out= &_dsetOut (drop=_mqRecordID_);
    by _mqRecordId_;
  run;
  
  *--> delete temp work datasets;
  Proc datasets nolist ddname=work;
    delete _MQTemp01_ _MQTemp02_;
    quit;
  run;
%End;

%IF ("&_group" ne "0")  and ("&_group" ne "1") %Then %Do;
  *--> Sort the data back into its original form;
  proc sort data=_MQTempFinal_  out= &_dsetOut (drop=_mqRecordID_);
    by _mqRecordId_;
  run;

  *--> delete temp work datasets;
  Proc datasets nolist ddname=work;
    delete _MQTemp01_ _MQTemp02_ _mqTempFinal_;
    quit;
  run;
%End;

%End; *--> if _ties are low;


/************************************************************************************/
/* If users wants TIES=HIGH, begin calculating this option                          */
/************************************************************************************/
*--> if _ties are high;
%if %upcase("&_ties") = "HIGH" %then %do;
data _MQTemp01_;
    set &_dsetin;
    _mqRecordId_ + 1;
  run;

  proc sort data=_MQtemp01_;
    by descending &_MQVarIn1 ;
  run;


  *--> Calculate a simple rank _ties=low;
  data _MQTemp02_;
    set _MQTemp01_; 
    Retain _CurrentCount_ 0;
    by descending &_MQVarIn1 ;
    _CurrentCount_ + 1;

    if first.&_MQVarIn1 and &_MQVarIn1 ne lag(&_MQVarIn1) then do;
      &_MQRankVar + 1;
      _CurrentRank_=0;
    end;  


    if first.&_MQVarIn1 and &_MQVarIn1 eq lag(&_MQVarIn1) then do;
      &_MQRankVar + 1;
      _currentRank_ = _CurrentCount_;
    end;

    if (_currentcount_ eq &_MQRankVar ) and _currentrank_ = 0 then &_MQRankVar +0;
    if (_currentcount_ ne &_MQRankVar ) and _CurrentRank_ = 0 then &_MQRankVar = _CurrentCount_;

run;

  proc sort data=_MQTemp02_;
    by &_MQVarIn1 descending _currentCount_; 
  Run;

  data _MQTemp03_; 
    set _MQTemp02_;
    by &_MQVarIn1 ;
    retain _Rank;
      if first.&_MQVarIn1 then _Rank = _CurrentCount_;
    Drop _CurrentCount_ &_MQRankVar _CurrentRank_;
  Run;

  proc datasets nolist ddname=work;
    modify _mqtemp03_;
    rename _rank = &_mqrankVar;
    quit;
  Run;



/************************************************************************************/
/* If GROUPS option was specified, i.e. value > 1 then start calculating groups     */
/************************************************************************************/

%if ("&_group" ne "0")  and ("&_group" ne "1") %then %do;
*--> get the overall N;
  Proc Means data= _mqTemp03_  noprint;
    var &_mqRankVar;
    output out=_mqtemp04_  n=_nall_;
  Run;

  data _null_;
  set _mqTemp04_;
    call symput('nAll',_nall_);
  run;

    
  Proc delete data=_mqTemp04_; 
  Run;



  proc sort data=_mqtemp03_;  
    by &_MqRankVar ; 
  run;

  data _MqTempFinal_;
  set _mqTemp03_;
    %if ("&_group" ne "0") or ("&_group" ne "1") %then %do;
           _MqGroupRank_ = FLOOR( &_MqRankVar * &_Group /( &nall  + 1 ) );
         Drop &_MqRankVar;
      %end;
  run;
  
  *--> rename variables into proper names.;
  
  proc datasets nolist ddname=work;
  modify _mqTempFinal_;
  rename _mqGroupRank_ = &_mqRankVar ;
  quit;
  Run;
%end;

%if ("&_group" eq "0")  or ("&_group" eq "1") %then %do;
  *--> Sort the data back into its original form;
  proc sort data=_MQTemp03_ out= &_dsetOut (drop=_mqRecordID_);
    by _mqRecordId_;
  run;
  
  *--> delete temp work datasets;
  Proc datasets nolist ddname=work;
    delete _MQTemp01_ _MQTemp02_ _MQTemp03_;
    quit;
  run;
%end;

%if ("&_group" ne "0")  and ("&_group" ne "1") %then %do;
  *--> Sort the data back into its original form;
  proc sort data=_MQTempFinal_  out= &_dsetOut (drop=_mqRecordID_);
    by _mqRecordId_;
  run;

*--> delete all the temp datasets that were created and no longer needed.;
  *--> delete temp work datasets;
  Proc datasets nolist ddname=work;
    delete _MqTemp01_ _MQTemp02_ _MQTemp03_ _mqTempFinal_;
    quit;
  run;
%end;
*--> end groups option;

%end;

/************************************************************************************/
/* If users wants TIES=MEAN, begin calculating this option                          */
/************************************************************************************/

*--> when _ties = Mean and groups option;
%if %upCase("&_ties") EQ "MEAN" %then %do;

  data _MQtemp01_;
    set &_dsetin;
    _mqRecordId_ + 1;
  run;

  proc sort data= _MQtemp01_;
    by descending &_MQVarIn1;
  run;

  *--> Calculate a simple rank _ties=mean;
  data _MQTemp02_;
    set _MqTemp01_; 
    Retain _CurrentCount_ 0;
    by descending &_MQVarIn1;
    _CurrentCount_ + 1;

    if first.&_mqVarin1 and &_MQVarIn1 ne lag(&_MQVarin1) then do;
      &_MQRankVar + 1;
      _CurrentRank_ = 0;
    end;  

    if first.&_MQVarIn1 and &_MQVarin1 eq lag(&_MQVarin1) then do;
      &_MQRankVar + 1;
      _currentRank_ = _CurrentCount_;
    end;

    if (_currentcount_ eq &_MQRankVar ) and _currentrank_ = 0 then &_MQRankVar + 0;
    if (_currentcount_ ne &_MQRankVar ) and _CurrentRank_ = 0 then &_MQRankVar = _CurrentCount_;

 * Drop _CurrentCount_ _CurrentRank_ ;
run;



*--> Calc the mean for each rankgroup;
  Proc sort data = _mqTemp02_;
    by &_mqRankVar;
  Run;

  proc means data = _mqTemp02_ noprint;
    var _currentCount_;
    by &_MqRankVar;
    output out= _mqTemp03_ mean= _MqRankVar_ n= _nVal_;
  Run;

/************************************************************************************/
/* If GROUPS option was specified, i.e. value > 1 then start calculating groups     */
/************************************************************************************/
%if ("&_group" ne "0")  and ("&_group" ne "1") %then %do;
*--> get the overall N;
  Proc Means data= _mqTemp03_  noprint;
    var _nval_;
    output out=_mqtemp04_ sum=_nall_;
  run;

  data _null_;
  set _mqTemp04_;
    call symput('nAll',_nall_);
  run;

  Proc delete data=_mqTemp04_; 
  run;
%end;


  Proc Sort data= _mqTemp03_ ;
    by &_MqRankVar ;
  Run;

  proc sort data=_mqtemp02_;  
    by &_MqRankVar ; 
  Run;

  data _MqTempFinal_;
    merge _mqtemp02_(in=in1)
          _mqtemp03_(in=in2);
          by &_MqRankVar ;
      %if ("&_group" ne "0") and ("&_group" ne "1") %then %do;
         _MqGroupRank_ = FLOOR( _MqRankVar_ * &_Group /( &nall  + 1 ) );
         Drop _MqRankVar_;
      %end;
  Run;
      
     
*--> Sort the data back into its original form;
  proc sort data= _MqTempFinal_ out= &_dsetOut (drop=_mqRecordID_ _freq_ _type_ &_mqRankVar 
                                                    _CurrentRank_ _CurrentCount_ _nval_);
   by _mqRecordId_;
 Run;

 
*--> Rename the variables to their proper names; 
  Proc datasets nolist ddname=&libn;
    modify &_dsetnm;
     %if ("&_group" eq "0") or ("&_group" eq "1") %then %do;
        rename _MqRankVar_ = &_mqRankvar;
     %end;
     %if ("&_group" ne "0") and ("&_group" ne "1") %then %do;
        rename _MqGroupRank_  = &_mqRankVar;
     %end; 
    quit;
  Run;

*--> Delete all the temp datasets that were created and no longer needed. ;
  Proc datasets nolist ddname=work;
    delete _MQTemp01_ _MQTemp02_ _mqTempfinal_ _temp03_;
    quit;
  run;
/******************************************************************************************/
/* RANK is copyright (c) 2007 by MineQuest, LLC. All Rights Reserved.                     */
/* MineQuest, LLC, 1939 Queensbridge Dr., Columbus, OH USA.                               */
/* This Macro Program is proprietary software and is licensed property of MineQuest, LLC. */
/******************************************************************************************/ 
%end; *--> if _ties are Mean;

%mend Rank;


Proc delete data=work._all_;
run;

libname perm 'c:\sasdata';

data cake;
   input Name $ 1-10 Present 12-13 Taste 15-16;
   datalines;
Davis      77 84
Orlando    93 80
Ramey      68 72
Roe        68 75
Sanders    56 79
Simms      68 77
Strickland 82 79
Berry      91 69
Rack       91 69
;
run;

%rank(cake, perm.RankedData, High, 0,Taste, MyTasteRankVar);
Proc print data= perm.rankeddata;run;
/*
%rank(cake, perm.RankedData, Low, 0,Taste, MyTasteRankVar);
Proc print data= perm.rankeddata;run;
%rank(cake, perm.RankedData, Mean, 0,Taste, MyTasteRankVar);
Proc print data= perm.rankeddata;run;



%rank(cake, perm.RankedData, High, 3,Taste, MyTasteRankVar);
Proc print data= perm.rankeddata;run;
%rank(cake, perm.RankedData, Low, 3,Taste, MyTasteRankVar);
Proc print data= perm.rankeddata;run;
%rank(cake, perm.RankedData, Mean, 3,Taste, MyTasteRankVar);
Proc print data= perm.rankeddata;run;

*/