Lookup Values from another table

Reply
Contributor
Posts: 34

Lookup Values from another table

Hello,

 

Please help me figure out a better way to look up values. Below are the tables and the desired table

 

Table 1:

_Name_     Var1      Var2    Var3    Var4

DS             100         200     300      400

Grade          3             4         5           6

 

Table 2:

Grade    value

  3            0.1

  4            0.2

  5            0.3 

  6            0.4

 

Table Want:

_Name_     Var1      Var2    Var3    Var4

DS             100         200     300      400

Grade          3             4         5           6

Value         0.1           0.2     0.3        0.4

 

I have working solution by tranposing Table1 and then merging with Table2 and then transposing back the merged table. I am trying to reduce processing time as the datasets are big and the process is repeated multiple times. Any help is greatly appreciated.

 

Thanks

Super Contributor
Posts: 441

Re: Lookup Values from another table

It looks like you are trying to create a row of look-ed up values based on a row of lookup values. I'd say in that case your approach of transposing, performing the lookup and reverse transposing is a good approach.

 

Where it rubs it is because the data model is rather awkward and not well suited for analysis and reporting or basically anything. I'd go for the transposed model of table 1 and use that as the basis for subsequent processing.

 

Id DS Grade

1 100 3

2 200 4

3 300 5

4 400 6

 

Hope this helps,

- Jan.

Super User
Posts: 19,865

Re: Lookup Values from another table

Which is often why a long data structure is optimal over a wide data structure.  

 

Given your exact case it isn't hard to find workarounds, but if you say your data set is large I have a feeling there are other issues.

 

Is this exactly what you would do with your actual data, or are there other steps required as well. I have a strong suspicion that may change the answer.

Contributor
Posts: 34

Re: Lookup Values from another table

Hi Reeza,

 

There are more steps to follow after this process. Given the SAS data model, I agree the long form is more amenable.

 

Super User
Posts: 5,516

Re: Lookup Values from another table

Why not just transpose table 2 instead?  The results will be ready to combine with Table 1 in a DATA step.

Super User
Posts: 10,044

Re: Lookup Values from another table


If you didn't have a big table. Try double proc transpose .



data Table1;
input _Name_   $  Var1      Var2    Var3    Var4;
cards;
DS             100         200     300      400
Grade          3             4         5           6
;
run;
 
data Table2;
input Grade    value;
cards;
  3            0.1
  4            0.2
  5            0.3 
  6            0.4
;
run;
proc transpose data=table1 out=temp;
var var:;
id _name_;
run;
data temp1;
 merge table2 temp;
 by Grade    ;
run;
proc transpose data=temp1 out=want;
id _name_;
run;




Trusted Advisor
Posts: 2,116

Re: Lookup Values from another table

Depending on how big table 2 is, you could also attach this with PROC FORMAT.  Depending on your final needs, you may not even need to store the third row in your final data set.  Try this Google search for a number of examples:

proc format data lookup site:sas.com

Ask a Question
Discussion stats
  • 6 replies
  • 398 views
  • 0 likes
  • 6 in conversation