BookmarkSubscribeRSS Feed
xxz3231
Calcite | Level 5

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!

 

12 REPLIES 12
xxz3231
Calcite | Level 5

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
Super User

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
Calcite | Level 5
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
Super User

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
Obsidian | Level 7
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
Calcite | Level 5
Thank you very much!
DanZ
Obsidian | Level 7

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

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
Super User

Always test edge and boundary conditions 🙂

data_null__
Jade | Level 19

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;
   

Capture.PNG

hello_fj
Calcite | Level 5
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__
Jade | Level 19
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 12 replies
  • 1757 views
  • 5 likes
  • 6 in conversation