topic Re: Lookup Values from another table in SAS Programming

Lookup Values from another table

sai_ch — Thu, 31 Mar 2016 20:39:07 GMT

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

Re: Lookup Values from another table

jklaverstijn — Thu, 31 Mar 2016 20:58:37 GMT

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.

Re: Lookup Values from another table

Reeza — Thu, 31 Mar 2016 21:28:26 GMT

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.

Re: Lookup Values from another table

Astounding — Thu, 31 Mar 2016 21:30:35 GMT

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

Re: Lookup Values from another table

sai_ch — Fri, 01 Apr 2016 01:14:26 GMT

Hi Reeza,

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

Re: Lookup Values from another table

Ksharp — Fri, 01 Apr 2016 01:43:17 GMT


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;

Re: Lookup Values from another table

Doc_Duke — Fri, 01 Apr 2016 13:19:31 GMT

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