DATA Step, Macro, Functions and more

lookup technique

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

lookup technique

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%

Accepted Solutions
Solution
‎01-23-2018 11:45 AM
Super User
Posts: 10,574

Re: lookup technique

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎01-23-2018 11:45 AM
Super User
Posts: 10,574

Re: lookup technique

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 2

Re: lookup technique

Posted in reply to KurtBremser

It seems an effective solution. Thank you!

PROC Star
Posts: 1,401

Re: lookup technique

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

Respected Advisor
Posts: 4,797

Re: lookup technique

@draycut

Yes, kudos to @KurtBremser 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 @KurtBremser 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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 146 views
  • 0 likes
  • 4 in conversation