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
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.
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.
Hi Reeza,
There are more steps to follow after this process. Given the SAS data model, I agree the long form is more amenable.
Why not just transpose table 2 instead? The results will be ready to combine with Table 1 in a DATA step.
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;
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.