Odtnhj

I have two variables (varx and vary) in data set "dat" and need to create a final score, by first categorizing varx and vary, and then translate the score categories into a final score according to a look-up table "lookup".

I managed to get past the categorizing part and am now stuck on how to tell SAS to use the categories I created (i.e., "varxcat" and "varycat") as row and column indices of "lookup", grab the value I need for each observation, and put it into a final score variable (call it "score") in "dat".

In R (in which I normally code) this can easily be done with something like a for loop. Is there anything similar in SAS? (I don't must use "varxcat" and "varycat", just need to eventually create "score".)

data dat;
input ID $ varx vary;
datalines;
1 1 1
2 4 5
3 11 12
4 23 14
5 24 20
;

data lookup;
input x01to10 x11to20 x21to30;
datalines;
21 52 73
84 95 96
107 118 149
; /*first row is for y01to10, second row is for y11to20, and third row is for y21to30,
such that if someone's x score is in category 1 and y score is in category 3,
the person's final score should be 107*/

data dat;
set dat;
if varx <= 10 then varxcat = 1;
else if varx > 10 & varx <= 20 then varxcat = 2;
else if varx > 20 & varx <= 30 then varxcat = 3;
if vary <= 10 then varycat = 1;
else if vary > 10 & vary <= 20 then varycat = 2;
else if vary > 20 & vary <= 30 then varycat = 3;
run; 

Desired "dat" looks like

data dat;
input ID $ varx vary score;
datalines;
1 1 1 21
2 4 5 21
3 11 12 95
4 23 14 96
5 24 20 96
;

A lookup table for data value mapping is essentially a left join operation. SAS has a lot of ways to left join data, including

• SQL


• Merge


• Hash object


• Array (direct addressing)


• Formats


• Informats

Here are four ways: SQL, Merge, Array and Hash. The mapping from var* to category is done by the functional mapping int (value/10):

data have;
input ID $ varx vary;
datalines;
1 1 1
2 4 5
3 11 12
4 23 14
5 24 20
6 5 29 /* score should be 107 */
;

data lookup;
do index_y = 0 to 2;
do index_x = 0 to 2;
 input lookup_value @@;
 output;
end;
end;
datalines;
21 52 73
84 95 96
107 118 149
;

*------------------- SQL;

proc sql;
 create table want as
 select 
 id, lookup_value as score
 from 
 have
 left join 
 lookup
 on
 int (have.varx/10) = lookup.index_x
 and
 int (have.vary/10) = lookup.index_y
 order by 
 id
;

*------------------- MERGE;

data have2(index=(myindexname=(xcat ycat)));
 set have;
 xcat = int(varx/10);
 ycat = int(vary/10);
run;
proc sort data=lookup;
 by index_x index_y;

options msglevel=i;

data want2(keep=id lookup_value rename=(lookup_value=score));
 merge 
 have2(rename=(xcat=index_x ycat=index_y) in=left) 
 lookup
 ;
 by index_x index_y;
 if left;
run;

proc sort data=want2;
 by id;
run;

*------------------- ARRAY DIRECT ADDRESSING;

data want3;
 array lookup [0:2,0:2] _temporary_;
 if _n_ = 1 then do until (endlookup);
 set lookup end=endlookup;
 lookup[index_x,index_y] = lookup_value;
 end;

 set have;
 xcat = varx/10;
 ycat = vary/10;
 score = lookup[xcat,ycat];
 keep id score;
run;

*------------------- HASH LOOKUP;

data want4;
 if 0 then set lookup;
 if _n_ = 1 then do;
 declare hash lookup(dataset:'lookup');
 lookup.defineKey('index_x', 'index_y');
 lookup.defineData('lookup_value');
 lookup.defineDone();
 end;

 set have;

 index_x = int(varx/10);
 index_y = int(vary/10);

 if (lookup.find() = 0) then 
 score = lookup_value;

 keep id score;
run;