FIND VALUES FROM ANOTHER TABLE AND REPLACE

[SNAG]

I have used the import wizard, so now I have these tables:

Reglas- Here, we have CVE_CONCEPTO and FORMULA, the last mentioned are the ones that I want to replace with the values in the other table.

INFO-Here, we have CVE_CONCEPTO and SALDO, the last mentioned are the ones that would replace the FORMULA depending on the CVE_CONCEPTO.

But when I tried with this code, it only replaced the first value, not the ones after the + character.

DATA want;

SET Reglas;

DO i=1 to _nobs;

SET INFO nobs=_nobs point=i;

IF findw(FORMULA,Strip(CVE_CONCEPTO)) THEN FORMULA=tranwrd(FORMULA,Strip(CVE_CONCEPTO),Strip(SALDO));

END;

DROP CVE_CONCEPTO SALDO;

RUN;

The result I´m looking for is the following:

CVE_CONCEPTO| FORMULA

3 | 10+20

6 | 20+50

I´ll be very grateful if you could help me with this. Thanks

[Tom]

Your description is not very clear.  I cannot tell if you want to just merge two datasets or do something more complex. I cannot tell if you want to replace strings or do arithmetic.

Please share the example data as text and not as attached files.  Please show the desired result for the given example.

[SNAG]

Yes, sorry for the first explanation.

I´ve two tables REGLAS AND INFO, where I used the import wizard because they are XLSX.

Table REGLAS is like this:

CVE_CONCEPTO | FORMULA

3                             | 1+2

6                             | 4+5

Then I have INFO table like this:

CVE_CONCEPTO | SALDO

1                             | 10

2                             | 20

3                             | 

4                             | 20

5                             | 50

6                             | 

Desired Result:

CVE_CONCEPTO | FORMULA

3                             | 10+20

6                             | 20+50

I wish this is more clear, if not please let me know and I would try to explain it better

Thank you

[Tom]

So assuming you have character strings (which might be a problem if you are trying to use a spreadsheet as your data entry tool) are you saying the request is to replace "1" with "10" and "4" with "20"?  How do propose to avoid converting "14" into "1020"?

If the size of INFO table is small enough you could perhaps convert it into a format.  Let's say you created a format named $INFO that would convert "1" to "10" and "2" to "20" etc. then you could make a loop like:

data want;
  set REGLAS;
  length new_formula $200 ;
  do index=1 to countw(formula,'+');
    new_formula = catx('+',new_formula,put(scan(formula,index,'+'),$info.));
  end;
run;

[SNAG]

I get another result, like this:

CVE_CONCEPTO | FORMULA | NEW FORMULA | INDEX

3                             | 1+2            | 1+2                      | 3

6                             | 4+5            | 4+5                      | 3

1                             |                   |                             | 2

2                             |                   |                             | 2

4                             |                   |                             | 2

5                             |                   |                             | 2

The tables I do are an example, but the real one has 1,000 observations.

You are right; in table REGLAS CVE_CONCEPTO is numeric, and FORMULA is char.

CVE_CONCEPTO | FORMULA

3                             | 1+2

6                             | 4+5

On the other hand in table INFO both are numeric.

CVE_CONCEPTO | SALDO

1                             | 10

2                             | 20

3                             | 

4                             | 20

5                             | 50

6                             | 

So I don´t mind if in the desired result it gives me EX. 10+20 as a string that has been replaced or the value of that operation 30

CVE_CONCEPTO | FORMULA

3                             | 10+20 or 30

6                             | 20+50 or 70

[Tom]

It is much easier to manipulate text than to try to evaluate arbitrary equations stored as text.

You skipped the step of converting the INFO data into a format.  Since the variable is numeric let's make a numeric format, INFO, instead of the character format I talked about before.

data INFO;
  input CVE_CONCEPTO SALDO ;
cards;
1  10
2  20
3  .
4  20
5  50
6  .
;

data format;
 fmtname='INFO';
 set info(rename=(CVE_CONCEPTO=START saldo=LABEL)) end=eof;
run;

proc format cntlin=format ;
run;

Now you will need to convert the strings you pull out of FORMULA into numbers to be able to use the format.

data reglas;
  input CVE_CONCEPTO FORMULA $30.;
cards;
3 1+2
6 4+5
;

data want;
  set reglas;
  length word $30 new_formula $200;
  do index=1 to countw(formula,'+');
    word = scan(formula,index,'+');
    number = input(word,32.);
    if not missing(number) then word=put(number,info.);
    new_formula=catx('+',new_formula,word);
  end;
  drop word number index;
run;

Results

         CVE_                  new_
OBS    CONCEPTO    FORMULA    formula

 1         3         1+2       10+20
 2         6         4+5       20+50

[SNAG]

It works, and I´m grateful. Just one last question, the + could change to any operator (+,-,*,/....); how could this be implemented?

[SNAG]

What I mean is that this could change, for example:

TABLE REGLAS:

CVE_CONCEPTO | FORMULA

3                             | 1+2-4

6                             | (4+5)*2

And I want the same desired tables:

CVE_CONCEPTO | FORMULA

3                             | 10+20-20

6                             | (20+50)*20

[Tom]

---

@SNAG wrote:

What I mean is that this could change, for example:

TABLE REGLAS:

CVE_CONCEPTO | FORMULA

3                             | 1+2-4

6                             | (4+5)*2

And I want the same desired tables:

CVE_CONCEPTO | FORMULA

3                             | 10+20-20

6                             | (20+50)*20

---

It helps to explain the full problem from the beginning.  SCAN() is not really going to work as well for that as you won't really know what the separators were.

Try using CALL SCAN() instead.

data reglas;
  input CVE_CONCEPTO FORMULA $30.;
cards;
3 1+2-4
6 (4+5)*2
;

data want;
  set reglas;
  length word $30 new_formula $200;
  new_formula=formula;
  do count=1 to length(formula)/2 ;
    call scan(new_formula, count, position, length, , 'dko');
    if position=0 then leave;
    word=substrn(new_formula, position, length);
    word=put(input(word,32.),info.);
    new_formula=cats(substrn(new_formula,1,position-1),word,substrn(new_formula,position+length));
  end;
  drop word count position length;
run;

         CVE_                    new_
OBS    CONCEPTO    FORMULA     formula

 1         3       1+2-4      10+20-4
 2         6       (4+5)*2    (20+50)*20