Compare a variable with a vector

[xxz3231]

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!

[xxz3231]

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!

[Reeza]

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;

[xxz3231]

Thank you so much for your answer!
I have another question: The vector is generated from other procedure and stored in a dateset,I want to use it directly in this data step instead of changing them by hand each time I run the code,can I do it?Thank you very much!

[Reeza]

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;

[DanZ]

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;

[xxz3231]

Thank you very much!

[DanZ]

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;

[FreelanceReinh]

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 

1. TEST_3 < M[1]?
2. TEST_3 > M[10]?
3. TEST_3 = M[i] for some i?
4. there are i ne j with M[i]=M[j]?

Not surprisingly, the results of the two proposed algorithms differ in three of the above special cases: 1, 2 and 4.

[Reeza]

Always test edge and boundary conditions 🙂

[data_null__]

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;
   

[hello_fj]

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();


	

[data_null__]

You should look a the PROC FORMAT documentation regarding the CNTLIN parameter. I allows you to create a format/informat using data from a data set. Very useful and no need to involve macro language.