topic Re: lookup technique in SAS Programming

lookup technique

E_V_B — Tue, 23 Jan 2018 09:41:44 GMT

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%

Re: lookup technique

Kurt_Bremser — Tue, 23 Jan 2018 10:03:23 GMT

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.

Re: lookup technique

PeterClemmensen — Tue, 23 Jan 2018 14:27:43 GMT

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

Re: lookup technique

E_V_B — Tue, 23 Jan 2018 14:37:56 GMT

It seems an effective solution. Thank you!

Re: lookup technique

Patrick — Tue, 23 Jan 2018 15:13:27 GMT

@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.

Re: lookup technique

michokwu — Fri, 15 Mar 2019 20:31:58 GMT

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.