BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
E_V_B
Calcite | Level 5

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

 

ClassMATURITY% MATURITY012345
Satisfactory Q1-20082 ... Class      
Satisfactory Q2-20166 ... Excellent Q1-20170.00%0.00%0.00%0.00%0.00%0.00%
NA Q1-20112 ... Excellent Q2-20170.00%0.00%0.00%0.00%0.00%0.00%
Special mention Q1-20152 ... Excellent Q3-20170.00%0.00%0.00%0.00%0.00%0.00%
NA Q1-20152 ... Strong Q1-20170.00%0.00%0.00%0.00%0.00%0.00%
Special mention Q3-20172 ... Strong Q2-20170.00%0.00%0.00%0.00%0.00%0.00%
Special mention Q3-20177 ... Strong Q3-20170.00%0.00%0.00%0.00%0.00%0.00%
Special mention Q3-20172 ... Satisfactory Q1-20170.29%1.15%2.29%3.42%4.54%5.64%
NA Q4-20149 ... Satisfactory Q2-20170.23%0.92%1.84%2.75%3.64%4.53%
NA Q1-20082 ... Satisfactory Q3-20170.29%1.14%2.27%3.39%4.49%5.58%
NA Q1-200816 ... Weak Q1-20170.60%2.38%4.71%6.97%9.19%11.35%
NA Q1-200816 ... Weak Q2-20170.78%3.10%6.11%9.02%11.84%14.58%
NA Q3-20171 ... Weak Q3-20170.70%2.80%5.53%8.17%10.75%13.25%
    Special mention Q1-20177.54%30.17%51.23%65.95%76.22%83.39%
    Special mention Q2-20176.64%26.54%46.04%60.36%70.88%78.61%
    Special mention Q3-20177.12%28.48%48.85%63.42%73.84%81.29%
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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
Calcite | Level 5

It seems an effective solution. Thank you!

michokwu
Quartz | Level 8

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_nofd_codefd_title program_titlepr_code
20120000001502403  Aerospace Engineering2401
20120000001941501  Anthropology And Archeology5502
20120000001941401  Applied Mathematics3701
20120000002952401  Architectural Engineering2403
20120000003203701  Architecture1401
20120000003455502  Area Ethnic And Civilization Studies1501

 

I look forward to your feedback. Thank you so much.

PeterClemmensen
Tourmaline | Level 20

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

Patrick
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1262 views
  • 0 likes
  • 5 in conversation