lookup technique

[E_V_B]

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%

[Kurt_Bremser · Accepted solution]

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.

[E_V_B]

It seems an effective solution. Thank you!

[michokwu]

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.

[PeterClemmensen]

I'm not sure I understand.. What is the first and second table here?

[Patrick]

@PeterClemmensen

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.