BookmarkSubscribeRSS Feed
sai_ch
Obsidian | Level 7

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

6 REPLIES 6
jklaverstijn
Rhodochrosite | Level 12

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.

Reeza
Super User

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.

sai_ch
Obsidian | Level 7

Hi Reeza,

 

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

 

Astounding
PROC Star

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

Ksharp
Super User

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;




Doc_Duke
Rhodochrosite | Level 12

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1405 views
  • 0 likes
  • 6 in conversation