Quartz | Level 8

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

Hi @Zatere,

Use PROC RANK:

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

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

Hi @Zatere,

Use PROC RANK:

``````proc rank data=have out=want descending ties=high;
var amount;
ranks No_times_gr_eq;
run;``````
Onyx | Level 15

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

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Quartz | Level 8

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

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".
Onyx | Level 15

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

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Super User

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

``````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;``````
Onyx | Level 15

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

The power of correlated sub-queries! 🙂

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Discussion stats
• 6 replies
• 567 views
• 6 likes
• 4 in conversation