topic Re: Lookup value based on rows and columns in SAS Programming

Lookup value based on rows and columns

daniela13 — Fri, 20 Aug 2021 10:07:34 GMT

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.

Re: Lookup value based on rows and columns

andreas_lds — Fri, 20 Aug 2021 10:26:03 GMT

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.

Re: Lookup value based on rows and columns

daniela13 — Fri, 20 Aug 2021 10:39:05 GMT

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.

Re: Lookup value based on rows and columns

PaigeMiller — Fri, 20 Aug 2021 11:29:03 GMT

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.

Re: Lookup value based on rows and columns

Reeza — Fri, 20 Aug 2021 15:59:43 GMT

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;

Re: Lookup value based on rows and columns

daniela13 — Tue, 24 Aug 2021 14:17:43 GMT

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