Count the number of rows whose value is greater or equal than the value of current row

[Zatere]

Hi there,

I have the below dataset:

data have;
input amount;
datalines;
10
11
11
33
456
54
66
66
89
;
run;

I want to add a column showing how many rows in the dataset have greater or equal value than the current row:

data want;
input amount No_times_gr_eq;
datalines;
10 9
11 8
11 8
33 6
456 1
54 5
66 4
66 4
89 2
;
run;

Any help would be much appreciated.

Thanks.

[FreelanceReinh · Accepted solution]

Hi @Zatere,

Use PROC RANK:

proc rank data=have out=want descending ties=high;
var amount;
ranks No_times_gr_eq;
run;

[yabwon]

Two approaches with Hash Tables;

1) order like in the original dataset (two data reads):

data test1;
  declare hash H(ordered:"A");
  H.defineKey("amount");
  H.defineData("amount","K");
  H.defineDone();
  declare hiter I("H");
  do until(eof);
    set have end=eof;
    if H.find() then K=1;
                else K+1;
    H.replace();
  end;

  do while(0=I.prev());
    N+k;
    k=N;
    H.replace();
  end;

  do until(eof2);
    set have end=eof2;
    H.find();
    output;
  end;
  drop n;
run;

proc print data=test1;
run;

2) data in descending order (single data read):

data test2;
  declare hash H(ordered:"A");
  H.defineKey("amount");
  H.defineData("amount","K");
  H.defineDone();
  declare hiter I("H");
  do until(eof);
    set have end=eof nobs=nobs;
    if H.find() then K=1;
                else K+1;
    H.replace();
  end;

  do while(0=I.prev());
    N+k;
    do k=1 to k;
      output;
    end;
  end;
run;

proc print data=test2;
run;

Bart

[Zatere]

Hi and thanks for this reply. If I have many variables for which I want to make this calculation, is there any way to do so using hash tables? For example, if I had another column "volume".

[yabwon]

Hash table of hash tables will do the job here:

data have;
  input amount;
  volume = amount*123;
  reversed = 1000-amount;
datalines;
10
11
11
33
456
54
66
66
89
;
run;
proc print;
run;

%macro scoring(
 have=have
,variables=amount volume reversed
,result=result
);

data &result.;
  array v &variables.;

  declare hash H;
  declare hiter I;

  length vn $ 32;
  declare hash HoH();
  HoH.defineKey("vn");
  HoH.defineData("H","I");
  HoH.defineDone();
  declare hiter IHoH("HoH");

  do until(eof);
    set &have. end=eof;
    do over v;
      vn=vname(v);
      if HoH.CHECK() then 
          do;
              H = _NEW_ hash(ordered: "A");
                  H.DefineKey(vn);
                  H.DefineData(vn,"K");
                  H.DefineDone();
              I = _NEW_ hiter ("H");
              HoH.replace();
          end;
      HoH.find();
      if H.find() then K=1;
                  else K+1;
      H.replace();
    end;
  end;

  do over v;
    vn=vname(v);
    HoH.find();
    N=0;
    do while(0=I.prev());
      N+k;
      k=N;
      H.replace();
    end;
    H.output(dataset:cats("test3_B_",vn));
  end;

  do until(eof2);
    set &have. end=eof2;
    array scores[%sysfunc(countw(&variables.,%str( )))];
    do over v;
      vn=vname(v);
      HoH.find();
      H.find();
      scores[_I_]=K;
    end;
    output;
  end;

  keep &variables. scores:; 
run;
%mend scoring;

%scoring(
have=have
,variables=amount volume reversed notExistingVariable
,result=test3
)


proc print data=test3;
run;

Bart

[Ksharp]

data have;
input amount;
datalines;
10
11
11
33
456
54
66
66
89
;
run;

proc sql;
create table want as
select amount,(select count(*) from have where amount>=a.amount) as want
 from have as a;
 quit;

[yabwon]

The power of correlated sub-queries! 🙂