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.
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
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
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;
The power of correlated sub-queries! 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.