Hello,Everyone,
I got a problem as follows,
I have a vector with 10 values,say M= (10,20,30,40,50,60,70,100,120,130)
I also have a dataset with several variables,I want to compare each observation in one of the variables with each value in the vector above and create a new rank variable-RANK
For example, my dataset is
data scores;
input Name $ Test_1 Test_2 Test_3;
datalines;
Bill 187 97 103
Carlos 156 76 74
Monique 99 102 129
;
I want to compare Test_3 with M,for instance,
103 is between 8th(100) and 9th(120),then RANK=8;
74 is between 7th(70) and 8th(100),then RAND=7;
129 is between 9th(120) and 10th(130),then RANK=9;
For variable and vector are stored in different dataset,the length of the two are different,I don't know how to programming with it.
Thank you very much!
One more thing, I don't have IML,is it possible to solve it by using the basic SAS programming techniques?
Thank you very much!
Yes you can solve in Base SAS, I suggest setting up a temporary array and storing the values there. If your values are in a data set you can also load the temporary array from the dataset.
The temporary array is retained across the dataset so you can compare for each row.
data want;
set scores;
array M(10) _temporary_ (10,20,30,40,50,60,70,100,120,130);
rank=0;
do i=1 to 10 while (rank=0);
if m(i)> test_3 then rank=i-1;
end;
run;
Yes you can, as mentioned.
data scores;
input Name $ Test_1 Test_2 Test_3;
datalines;
Bill 187 97 103
Carlos 156 76 74
Monique 99 102 129
;
data M;
do i=10,20,30,40,50,60,70,100,120,130;
output;
end;
run;
data want;
array M(10) _temporary_;
if _n_=1 then do j=1 to 10;
set M;
M(j)=i;
end;
set scores;
rank=0;
do i=1 to 10 while (rank=0);
if m(i)> test_3 then rank=i-1;
end;
drop i j;
run;
data want(drop=i);
set have;
array M {10} _temporary_ (10,20,30,40,50,60,70,100,120,130);
do i = 1 to 10;
if M{i} > test_3 then leave;
end;
rank=i-1;
run;
If you have a table with different vectors for each key value, then you'll need to attach them somehow, and load those values into an array. You can use whatever you'd like, but I prefer hashes.
data vector;
input Name $ M_1 - M_10;
datalines;
Bill 10 20 30 40 50 60 70 100 120 130
Carlos 10 20 30 40 50 60 70 100 120 130
Monique 10 20 30 40 50 60 70 100 120 130
;
run;
data want(drop= rc i m_1 - m_10);
if _n_ = 1 then do;
if 0 then set vector;
declare hash v (dataset:'vector');
v.definekey('name');
v.definedata(all:'y');
v.definedone();
end;
call missing (of _all_);
set have;
rc=v.find();
array a {10} M_1 - M_10;
do i = 1 to 10;
if a{i} > test_3 then leave;
end;
rank=i-1;
run;
So, you've already got two solutions (I'm referring to the two initial answers). However, your specifications were incomplete: How should RANK be defined if
Not surprisingly, the results of the two proposed algorithms differ in three of the above special cases: 1, 2 and 4.
Always test edge and boundary conditions 🙂
If you make the LBOUND of the array 0 then you can use a "bodyless do".
data m;
infile cards dsd;
input m @@;
cards;
10,20,30,40,50,60,70,100,120,130
;;;;
run;
%let r0=%eval(&sysnobs-1);
proc transpose out=mwide prefix=m;
var m;
run;
proc print;
run;
data scores;
input Name $ Test_1 Test_2 Test_3;
datalines;
Bill 187 97 103
Carlos 156 76 74
Monique 99 102 129
;
proc print;
run;
data rank;
if _n_ eq 1 then set mwide(keep=m:);
array _m[0:&r0] m:;
drop m:;
set scores;
do rank = lbound(_m) to hbound(_m) until(test_3 le _m[rank]); end;
run;
proc print;
run;
data scores;
input Name $ Test_1 Test_2 Test_3;
datalines;
Bill 187 97 103
Carlos 156 76 74
Monique 99 102 129
;
data m;
input m @@;
cards;
10 20 30 40 50 60 70 100 120 130
;
%macro tt();
%do i=1 %to 10;
data _null_;
set m;
if _n_=&i. then call symput("a&i.",m);
run;
%end;
proc format;
value score %do i=1 %to 9 ; %do j=2 %to 10; %if %eval(&j.-&i.)=1 %then &&a&i -< &&a&j = "&i." ; %end; %end; ; ;
quit;
data scores;
set scores;
test_3_rank=put(test_3,score.);
run;
%mend;
%tt();
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: