Lookup value based on rows and columns

[daniela13]

Hi all, 

I am trying to recover a value from a different table based on two conditions: age that is stored in one variable and product which is stored in different columns.

As an example:

I have table A that contains the data below (a lot more values in my actual data base):

Age	PROD_A	PROD_B	PROD_C
18	0,0666%	0,0590%	0,0324%
19	0,0687%	0,0692%	0,0324%
20	0,0698%	0,0753%	0,0324%
21	0,0678%	0,0733%	0,0324%
22	0,0740%	0,0601%	0,0324%
23	0,0740%	0,0724%	0,0324%
24	0,0700%	0,0633%	0,0324%
25	0,0814%	0,0643%	0,0324%
26	0,0855%	0,0685%	0,0324%
27	0,0928%	0,0716%	0,0324%
28	0,0929%	0,0727%	0,0324%
29	0,1023%	0,0819%	0,0324%
30	0,1117%	0,0902%	0,0324%

And I have another table B where I have the age and product.  I want to recover the percentage from table A like this:

Age	Prod	%
25	PROD_A	0,0814%
29	PROD_C	0,0324%
26	PROD_B	0,0685%
18	PROD_C	0,0324%
24	PROD_C	0,0324%
26	PROD_B	0,0685%
30	PROD_A	0,1117%
27	PROD_A	0,0928%
19	PROD_C	0,0324%
25	PROD_B	0,0643%
22	PROD_C	0,0324%
23	PROD_C	0,0324%
25	PROD_B	0,0643%

Do you have any suggestions on how I can do this? I am new to SAS and until now I tried only with join and did not find any solution. I am trying to do this in SAS Enterprise Guide 7.1.

[andreas_lds · Accepted solution]

I would start by transposing the first dataset, so that is in the same form as the second dataset. Then a merge to combine both datasets.

proc transpose data=tab1 out=trans name=Prod;
   by Age;
   var Prod:; /* shifts all variables starting with "Prod" into rows */
run;

The code for the merge largely depends on what you expect as result, but i don't understand what you want.

[daniela13]

Thank you for you answer!
I want for every entry in table B to lookup the corresponding percentage from table A based on the age (ex: 25) and type of product (ex: PROD_A).
I cannot do a simple join between the two tables as the value from which column I need to recover depends on the type of product.
I hope this time is clearer.

[PaigeMiller]

If you performed the PROC TRANSPOSE that was in the code provided by @andreas_lds , you should be able to look at the resulting data set and from there try to do the merge.

[daniela13]

Thank you very much, it helped to get the values I wanted!

[Reeza]

proc transpose data = tableA out=TableA_LONG (rename = _name_ = PROD) prefix=PERCENT;
by AGE;
var PROD_A PROD_B PROD_C;
run;

proc sort data=tableA_long;
by age prod;
run;

data want;
merge TableB TableA_LONG;
by age prod;
run;

or use a merge on age and VVALUEX function but it returns a character value, not a numeric value. 

data want;
merge tableA TableB;
by Age;
lookup_value = input(vvaluex(prod), best.);
run;