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.
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.
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.
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.