How to make a INDEX and Match (EXCEL) in SAS?

[Holmes]

Hi guys!

I need a big help from you! I have two datasets:

First:

data TABLE_TEST;
input CLASSE $ NUM_3 NUM_4 NUM_5 NUM_6 NUM_7 NUM_8 NUM_9 NUM_10 NUM_11 NUM_12;
datalines;
R1 6.8 6.4 6.0 5.6 5.3 5.0 4.7 4.4 4.2 4.0
R2 6.9 6.5 6.1 5.7 5.4 5.0 4.8 4.5 4.3 4.1
R3 7.0 6.6 6.2 5.8 5.4 5.1 4.9 4.6 4.4 4.2
R4 7.1 6.7 6.3 5.9 5.6 5.3 5.0 4.7 4.6 4.3
R5 7.2 6.8 6.4 6.0 5.7 5.4 5.1 4.8 4.6 4.4
R6 7.4 7.0 6.6 6.2 5.8 5.5 5.3 5.0 4.8 4.6
R7 7.5 7.1 6.7 6.3 6.0 5.7 5.4 5.1 5.0 4.7
R8 7.7 7.3 6.9 6.5 6.2 5.8 5.6 5.3 5.1 4.9
R9 7.8 7.4 7.0 6.6 6.3 6.0 5.8 5.4 5.3 5.0
R10 8.0 7.6 7.2 6.8 6.5 6.2 5.9 5.6 5.4 5.2
;
run;

Second:

data TABLE_HAVE;
input ID $ CLASSE $ NUM_ $;
datalines;
S1 R1 NUM_3
S2 R2 NUM_6
S3 R3 NUM_12
S4 R4 NUM_10
S5 R5 NUM_3
S6 R6 NUM_11
S7 R7 NUM_6
S8 R8 NUM_7
S9 R9 NUM_8
S10 R10 NUM_9
;
run;

I need look for value in TABLE_TEST and bring to TABLE_HAVE
For exemple, S1 needs to return 6.8 because he is a "R1" and "Num_3".

What I want:

data TABLE_HAVE_FINAL;
input ID $ RESULT;
datalines;
S1 6.8
S2 5.7
S3 4.2
S4 4.6
S5 7.2
S6 4.8
S7 6.3
S8 6.2
S9 6.0
S10 5.9
;
run;

Can you help me?

[Reeza]

data want;
merge table_have table_test;
by CLASSE; 

Result= vvaluex(NUM_);

drop NUM_3-NUM_12;
run;

You may need to sort your data ahead of time, but something like the above should work for you.

---

@Holmes wrote:

Hi guys!

I need a big help from you! I have two datasets:

First:

data TABLE_TEST;
input CLASSE $ NUM_3 NUM_4 NUM_5 NUM_6 NUM_7 NUM_8 NUM_9 NUM_10 NUM_11 NUM_12;
datalines;
R1 6.8 6.4 6.0 5.6 5.3 5.0 4.7 4.4 4.2 4.0
R2 6.9 6.5 6.1 5.7 5.4 5.0 4.8 4.5 4.3 4.1
R3 7.0 6.6 6.2 5.8 5.4 5.1 4.9 4.6 4.4 4.2
R4 7.1 6.7 6.3 5.9 5.6 5.3 5.0 4.7 4.6 4.3
R5 7.2 6.8 6.4 6.0 5.7 5.4 5.1 4.8 4.6 4.4
R6 7.4 7.0 6.6 6.2 5.8 5.5 5.3 5.0 4.8 4.6
R7 7.5 7.1 6.7 6.3 6.0 5.7 5.4 5.1 5.0 4.7
R8 7.7 7.3 6.9 6.5 6.2 5.8 5.6 5.3 5.1 4.9
R9 7.8 7.4 7.0 6.6 6.3 6.0 5.8 5.4 5.3 5.0
R10 8.0 7.6 7.2 6.8 6.5 6.2 5.9 5.6 5.4 5.2
;
run;

Second:

data TABLE_HAVE;
input ID $ CLASSE $ NUM_ $;
datalines;
S1 R1 NUM_3
S2 R2 NUM_6
S3 R3 NUM_12
S4 R4 NUM_10
S5 R5 NUM_3
S6 R6 NUM_11
S7 R7 NUM_6
S8 R8 NUM_7
S9 R9 NUM_8
S10 R10 NUM_9
;
run;

I need look for value in TABLE_TEST and bring to TABLE_HAVE
For exemple, S1 needs to return 6.8 because he is a "R1" and "Num_3".

What I want:

data TABLE_HAVE_FINAL;
input ID $ RESULT;
datalines;
S1 6.8
S2 5.7
S3 4.2
S4 4.6
S5 7.2
S6 4.8
S7 6.3
S8 6.2
S9 6.0
S10 5.9
;
run;

Can you help me?

---

[ballardw]

This works, with slightly different output order because of sort needed, for your example:

proc sort data=table_test;
  by classe;
run;
proc sort data=table_have;
  by classe;
run;

data want;
   merge table_have table_test;
   by classe;
   array r (12) num_1-num_12;
   result = r[input(scan(num_,2,'_'),2.)];
   keep id result;
run;

The sort is needed because the BY statement requires a sorted order and unfortunately will expect R10 to be immediately after R1. Or use values like R01 R02 etc.

The Array creates a shorthand way of addressing variables such as Num_1, Num_2, Num_3 as R[1], R[2], R[3].

The extra variables I included so that if you get the number from the Num_ value then you can use that to reference the correct sequential variable. Otherwise you get into calculating offsets and if you had slightly more complex Num_ names, like Num_27 without 23 and 18 the offsets might get tricky. This approach adds in variables with missing values that you would generally ignore or drop.

The stuff inside the r[ ] selects the value after the underscore and turns it into a number to use as the index of the array. 

Keep indicates which variables you want in the output.

---

@Holmes wrote:

Hi guys!

I need a big help from you! I have two datasets:

First:

data TABLE_TEST;
input CLASSE $ NUM_3 NUM_4 NUM_5 NUM_6 NUM_7 NUM_8 NUM_9 NUM_10 NUM_11 NUM_12;
datalines;
R1 6.8 6.4 6.0 5.6 5.3 5.0 4.7 4.4 4.2 4.0
R2 6.9 6.5 6.1 5.7 5.4 5.0 4.8 4.5 4.3 4.1
R3 7.0 6.6 6.2 5.8 5.4 5.1 4.9 4.6 4.4 4.2
R4 7.1 6.7 6.3 5.9 5.6 5.3 5.0 4.7 4.6 4.3
R5 7.2 6.8 6.4 6.0 5.7 5.4 5.1 4.8 4.6 4.4
R6 7.4 7.0 6.6 6.2 5.8 5.5 5.3 5.0 4.8 4.6
R7 7.5 7.1 6.7 6.3 6.0 5.7 5.4 5.1 5.0 4.7
R8 7.7 7.3 6.9 6.5 6.2 5.8 5.6 5.3 5.1 4.9
R9 7.8 7.4 7.0 6.6 6.3 6.0 5.8 5.4 5.3 5.0
R10 8.0 7.6 7.2 6.8 6.5 6.2 5.9 5.6 5.4 5.2
;
run;

Second:

data TABLE_HAVE;
input ID $ CLASSE $ NUM_ $;
datalines;
S1 R1 NUM_3
S2 R2 NUM_6
S3 R3 NUM_12
S4 R4 NUM_10
S5 R5 NUM_3
S6 R6 NUM_11
S7 R7 NUM_6
S8 R8 NUM_7
S9 R9 NUM_8
S10 R10 NUM_9
;
run;

I need look for value in TABLE_TEST and bring to TABLE_HAVE
For exemple, S1 needs to return 6.8 because he is a "R1" and "Num_3".

What I want:

data TABLE_HAVE_FINAL;
input ID $ RESULT;
datalines;
S1 6.8
S2 5.7
S3 4.2
S4 4.6
S5 7.2
S6 4.8
S7 6.3
S8 6.2
S9 6.0
S10 5.9
;
run;

Can you help me?

---

[Ksharp]

data TABLE_TEST;
input CLASSE $ NUM_3 NUM_4 NUM_5 NUM_6 NUM_7 NUM_8 NUM_9 NUM_10 NUM_11 NUM_12;
datalines;
R1 6.8 6.4 6.0 5.6 5.3 5.0 4.7 4.4 4.2 4.0
R2 6.9 6.5 6.1 5.7 5.4 5.0 4.8 4.5 4.3 4.1
R3 7.0 6.6 6.2 5.8 5.4 5.1 4.9 4.6 4.4 4.2
R4 7.1 6.7 6.3 5.9 5.6 5.3 5.0 4.7 4.6 4.3
R5 7.2 6.8 6.4 6.0 5.7 5.4 5.1 4.8 4.6 4.4
R6 7.4 7.0 6.6 6.2 5.8 5.5 5.3 5.0 4.8 4.6
R7 7.5 7.1 6.7 6.3 6.0 5.7 5.4 5.1 5.0 4.7
R8 7.7 7.3 6.9 6.5 6.2 5.8 5.6 5.3 5.1 4.9
R9 7.8 7.4 7.0 6.6 6.3 6.0 5.8 5.4 5.3 5.0
R10 8.0 7.6 7.2 6.8 6.5 6.2 5.9 5.6 5.4 5.2
;
run;

data TABLE_HAVE;
input ID $ CLASSE $ NUM_ $;
datalines;
S1 R1 NUM_3
S2 R2 NUM_6
S3 R3 NUM_12
S4 R4 NUM_10
S5 R5 NUM_3
S6 R6 NUM_11
S7 R7 NUM_6
S8 R8 NUM_7
S9 R9 NUM_8
S10 R10 NUM_9
;
run;

proc sort data=TABLE_TEST;by CLASSE;run;
proc transpose data=TABLE_TEST out=key;
by CLASSE;
var _numeric_;
run;
data key;
 set key(rename=(col1=result));
 classe=upcase(classe);
 _NAME_=upcase(_NAME_);
run;
data want;
 if _n_=1 then do;
  if 0 then set key;
  declare hash h(dataset:'Key');
  h.definekey('classe','_NAME_');
  h.definedata('result');
  h.definedone();
 end;
set TABLE_HAVE;
call missing(result);
rc=h.find(key:upcase(classe),key:upcase(NUM_));
drop rc _name_;
run;