BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Zatere
Quartz | Level 8

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
FreelanceReinh
Jade | Level 19

Hi @Zatere,

 

Use PROC RANK:

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

View solution in original post

6 REPLIES 6
FreelanceReinh
Jade | Level 19

Hi @Zatere,

 

Use PROC RANK:

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

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



Zatere
Quartz | Level 8
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
Onyx | Level 15

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



Ksharp
Super User
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
Onyx | Level 15

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



sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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