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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.