Find offer amnt and offer model from concatenated fields

[Ronein]

Hello

I have  2 data sets:

1- Loans that customer took (Fields-ID(Customer ID),loan model, loan amount

2-Offers for loans that customers received(Fields-ID (Customer ID),loans models offered, loans amounts offered.As can see these field :loans models offered, loans amounts offered are concatenate of numbers with delimiter '-'.

I would like to merge the two data sets and find:

What was the amount offered for the loan customer took

For this I want to calculate 2 fields:

New field 1

Location of value of loan model(data set 1)  in field loans models offered(data set 2).

For example:

For ID=1 loan models offered are "987-712"  and loan model taken is  712 so the value of the new field should be 2

For ID=2 loan models offered are is '888' and loan model taken is '888' so the value of new field should be 1

For ID=3 loan models offered are is '666' and loan model taken is '222' so the value of new field should be 0

New field 2

Based on value of new field 1 I want to find the specific offer amount.

For example:

For ID=1 offer is 2000

For ID=2 offer is  5000

For ID=2 offer is  NULL

What is the way to calculated the two desired fields please?

Data tbl1;
infile datalines delimiter=',';
informat offer_amounts $200. offer_models $200.;
Input ID offer_amounts $ offer_models $;
cards;
1,1000-2000,987-712
2,5000,888
3,3000,666
4,4000-2000,333-421
5,6000-3000,555-431
6,9000,777
;
Run;


Data tbl2;
input ID loan_Model loan_amount;
cards;
1 712 1700
2 888 5000
3 222 8000
4 333 4000
5 431 5000
6 777 2000
;
Run;


proc sql;
create table want as
select a.*,b.offer_amounts,b.offer_models
from tbl2 as a
left join tbl1 as b
on a.ID=b.ID
;
quit;
/***step1-Search loan_Model in field offer_models and find its position***/
/**step2-Find the relevant offer based on the position found in step1**/
/*ID=1, position 2---- offer 2000*/
/*ID=2, position 1-----offer 5000*/
/*ID=3, position 0-----offer Null*/
/*ID=4, position 1-----offer 4000*/
/*ID=5, position 2-----offer 5000*/
/*ID=5, position 1-----offer 9000*/

 

[ballardw]

Since the code you show for your TBL1 has values of "offer_amount" of 1000-200  4000-200 and 6000-300 you may need to walk us through more details on where we find values like 2000 (Input variable $ defaults to 8 character length. Your shown values are 9 characters so fix the code).

You don't do us or yourself any favors if we are supposed to look for a "loan model taken" as numeric inside a character variable like Offer_models. If you are not doing arithmetic with the "load model taken" variable it really should not be numeric at all.

FINDW function can return word order, if found, such as 1, 2 or 3 instead of position of characters using the E option.

If you are going to look for a numeric value in a character variable then you have to take responsibility for the conversion of the numeric to an appropriate character value for the search.

[Ronein]

Sorry

Data tbl1;
infile datalines delimiter=',';
informat offer_amounts $200. offer_models $200.;
Input ID offer_amounts $ offer_models $;
cards;
1,1000-2000,987-712
2,5000,888
3,3000,666
4,4000-2000,333-421
5,6000-3000,555-431
6,9000,777
;
Run;

[Ronein]

Can you please show code?

FINDW function provide me the character location but I need word location .

For example:

Word "712" location in "987-712"  should be 2  

and then I can get offer "2000 "  from "1000-2000"   using SCAN function with delimiter '-'

[Ronein]

There is also problem- when the concatenate value has no delimiter (in case that it have only one word) then the findW  with 'E' option doesnt calculate well

[ballardw · Accepted solution]

---

@Ronein wrote:

There is also problem- when the concatenate value has no delimiter (in case that it have only one word) then the findW  with 'E' option doesnt calculate well

---

Lets see your code and describe what "doesn't calculate well" means.

See if this gives you ideas:

data example;
   merge tbl1 tbl2;
   by id;
   model_position = findw(strip(offer_models),strip(put(loan_model,6. -L)),'-','E');
   if model_position>0 then loan_offer  = scan(offer_amounts,model_position,'- ');
run;

Remember SAS pads values with trailing blanks of character values when longer than the defined length.

If you don't consider those and only use '-' as the delimiter in FINDW then you need need to STRIP (or old school LEFT(trim(variable)) to make sure there are not leading/trailing blanks in the value that would be used in the comparison. "1000        " is not equal to "1000".  You could likely drop the strip for Offer_models but would need to add a space to the list of delimiters so the trailing blanks are not considered in the position determination. Scan will have a similar problem as well as not liking a position of 0 (or missing) for which value to find.

[Ronein]

Thanks so much,

Can you please explain again why is it essential to remove leading/trail blanks from first argument of FINDW?

For the second argument I understand why need to remove  leading/trail blanks because actually it is the value we are looking for.

findw(strip(offer_models),strip(put(loan_model,6. -L)),'-','E')

[Tom]

---

@Ronein wrote:

Thanks so much,

Can you please explain again why is it essential to remove leading/trail blanks from first argument of FINDW?

For the second argument I understand why need to remove  leading/trail blanks because actually it is the value we are looking for.

findw(strip(offer_models),strip(put(loan_model,6. -L)),'-','E')

---

This issue is the spaces padded onto the end of the variable with the hyphen delimited list.  If you look for the word '712' and the string has the value '987-712     '  then it will not find. 

But the FINDW() function has a modifier to trim the arguments.  

data tbl1;
  length offer_amounts offer_models $200;
  input ID offer_amounts offer_models ;
cards;
1 1000-2000 987-712
2 5000      888
3 3000      666
4 4000-2000 333-421
5 6000-3000 555-431
6 9000      777
;

data tbl2;
  input ID loan_Model loan_amount;
cards;
1 712 1700
2 888 5000
3 222 8000
4 333 4000
5 431 5000
6 777 2000
;

data example;
  merge tbl1 tbl2;
  by id;
  model_position = findw(offer_models,cats(loan_model),'-','Et');
  if model_position>0 then loan_offer  = input(scan(offer_amounts,model_position,'-'),32.);
run;

Note:  CATS() will convert a number to a string without any leading or trailing spaces.  

Note:  STRIP() will remove leading and trailing spaces.  TRIM(LEFT()) can be used as a substitute for STRIP() but LEFT(TRIM()) cannot because that will just move the leading spaces to trailing spaces.

For your real problem you should convert TBL1 to have one row per offer.

data offers;
  set tbl1;
  do index=1 to countw(offer_amounts,'-');
    loan_model = input(scan(offer_models,index,'-'),32.);
    loan_offer = input(scan(offer_amounts,index,'-'),32.);
    output;
  end;
run;

Then you just need to merge/join on ID and LOAN_MODEL.

proc sql;
create table want as 
select * 
from offers natural right join tbl2
;
quit;