topic Re: Count the number of rows whose value is greater or equal than the value of current row in SAS Programming

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

Zatere — Thu, 28 Mar 2024 10:34:42 GMT

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.

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

FreelanceReinh — Thu, 28 Mar 2024 10:56:25 GMT

Hi @Zatere,

Use PROC RANK:

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

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

yabwon — Thu, 28 Mar 2024 12:41:30 GMT

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

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

Zatere — Thu, 28 Mar 2024 12:50:24 GMT

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".

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

yabwon — Thu, 28 Mar 2024 13:39:48 GMT

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

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

Ksharp — Fri, 29 Mar 2024 03:01:36 GMT

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;

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

yabwon — Fri, 29 Mar 2024 13:32:56 GMT

The power of correlated sub-queries! 🙂