Hello everyone,
I am trying to find a lookup technique by which i can lookup data by more than one criteria. Something similar as the Vlookup formula with integrated MATCH function in Excel.
As you can see below, i want to populate the % column in the first table from the second table, using the Class and the INITIAL MATURITY as a key.
I am using SAS Enterprise Guide.
Thank you
Class | MATURITY | % | MATURITY | 0 | 1 | 2 | 3 | 4 | 5 | |
Satisfactory Q1-2008 | 2 | ... | Class | |||||||
Satisfactory Q2-2016 | 6 | ... | Excellent Q1-2017 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |
NA Q1-2011 | 2 | ... | Excellent Q2-2017 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |
Special mention Q1-2015 | 2 | ... | Excellent Q3-2017 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |
NA Q1-2015 | 2 | ... | Strong Q1-2017 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |
Special mention Q3-2017 | 2 | ... | Strong Q2-2017 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |
Special mention Q3-2017 | 7 | ... | Strong Q3-2017 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |
Special mention Q3-2017 | 2 | ... | Satisfactory Q1-2017 | 0.29% | 1.15% | 2.29% | 3.42% | 4.54% | 5.64% | |
NA Q4-2014 | 9 | ... | Satisfactory Q2-2017 | 0.23% | 0.92% | 1.84% | 2.75% | 3.64% | 4.53% | |
NA Q1-2008 | 2 | ... | Satisfactory Q3-2017 | 0.29% | 1.14% | 2.27% | 3.39% | 4.49% | 5.58% | |
NA Q1-2008 | 16 | ... | Weak Q1-2017 | 0.60% | 2.38% | 4.71% | 6.97% | 9.19% | 11.35% | |
NA Q1-2008 | 16 | ... | Weak Q2-2017 | 0.78% | 3.10% | 6.11% | 9.02% | 11.84% | 14.58% | |
NA Q3-2017 | 1 | ... | Weak Q3-2017 | 0.70% | 2.80% | 5.53% | 8.17% | 10.75% | 13.25% | |
Special mention Q1-2017 | 7.54% | 30.17% | 51.23% | 65.95% | 76.22% | 83.39% | ||||
Special mention Q2-2017 | 6.64% | 26.54% | 46.04% | 60.36% | 70.88% | 78.61% | ||||
Special mention Q3-2017 | 7.12% | 28.48% | 48.85% | 63.42% | 73.84% | 81.29% |
First I'd transpose the second table, so I get a new column maturity and the values in sequential order.
Then I'd create a combined key from class and maturity, and use the resulting dataset to create a format.
This format can then be applied to a similarly created combined key in table (dataset) 1.
First I'd transpose the second table, so I get a new column maturity and the values in sequential order.
Then I'd create a combined key from class and maturity, and use the resulting dataset to create a format.
This format can then be applied to a similarly created combined key in table (dataset) 1.
It seems an effective solution. Thank you!
Hi,
I have a similar challenge to this post and have not been able to resolve. Please can you provide guidance?
Below are two datasets with columns for: individual serial number, 1st degree code, programs title, program code. Each program code is mapped to the corresponding program title. For each individual, only the 1st degree code(fd_code) is given.
First I want to assign the pr_code to the corresponding program_title,
Second, I want to look up the fd_code and populate fd_title with program title
Serial_no | fd_code | fd_title | program_title | pr_code | |
2012000000150 | 2403 | Aerospace Engineering | 2401 | ||
2012000000194 | 1501 | Anthropology And Archeology | 5502 | ||
2012000000194 | 1401 | Applied Mathematics | 3701 | ||
2012000000295 | 2401 | Architectural Engineering | 2403 | ||
2012000000320 | 3701 | Architecture | 1401 | ||
2012000000345 | 5502 | Area Ethnic And Civilization Studies | 1501 |
I look forward to your feedback. Thank you so much.
I'm not sure I understand.. What is the first and second table here?
Yes, kudos to @Kurt_Bremser that he could understand this question and figure out the two tables. But given the OP's response all seems to be fine.
@E_V_B If your question is answered then please mark @Kurt_Bremser post as the answer. And going forward please be nice with us and try to post questions in a way that not only the selected few are able to make sense out of them.
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.