topic Re: Compare a variable with a vector in SAS Programming

Compare a variable with a vector

xxz3231 — Tue, 26 Jan 2016 17:11:33 GMT

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!

Re: Compare a variable with a vector

xxz3231 — Tue, 26 Jan 2016 17:15:01 GMT

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!

Re: Compare a variable with a vector

Reeza — Tue, 26 Jan 2016 17:20:10 GMT

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;

Re: Compare a variable with a vector

DanZ — Tue, 26 Jan 2016 18:47:34 GMT

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;

Re: Compare a variable with a vector

xxz3231 — Tue, 26 Jan 2016 17:33:28 GMT

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!

Re: Compare a variable with a vector

xxz3231 — Tue, 26 Jan 2016 17:33:55 GMT

Thank you very much!

Re: Compare a variable with a vector

Reeza — Tue, 26 Jan 2016 18:13:54 GMT

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;

Re: Compare a variable with a vector

DanZ — Tue, 26 Jan 2016 18:20:14 GMT

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;

Re: Compare a variable with a vector

FreelanceReinh — Tue, 26 Jan 2016 18:31:17 GMT

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

TEST_3 < M[1]?
TEST_3 > M[10]?
TEST_3 = M[i] for some i?
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.

Re: Compare a variable with a vector

Reeza — Tue, 26 Jan 2016 18:34:44 GMT

Always test edge and boundary conditions 🙂

Re: Compare a variable with a vector

data_null__ — Tue, 26 Jan 2016 18:37:21 GMT

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;

Re: Compare a variable with a vector

hello_fj — Wed, 27 Jan 2016 14:36:44 GMT

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

Re: Compare a variable with a vector

data_null__ — Wed, 27 Jan 2016 14:57:11 GMT

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.