BalanceLine Method

[DiogoSilvaBR]

Good morning everyone!

First of all I apologize because this text was translated by Google Translator and may contain translation errors.

I am looking for information on a method called BalanceLine but I do not find any reference on the subject so I bring it to you.

The general concept of BalanceLine is to read records of two ordered tables that have some logic resulting in a match process that resembles "Merge" but without it being possible for a record to match with more than one record from the other table.

Exemplifying

D1 FIELD A A B C C E E F

D2 FIELD A B C C D E F G

Applying a Merge the result would be:

D3

FIELD

A

A

B

C

C

C

C

E

E

F

However applying "BalanceLine" would return:

D3

FIELD

A

B

C

C

E

E

F

The logic of "BalanceLine" follows:

D1 FIELD A A B C C E E F

D2 FIELD A B C C D E F G

The Balance line checks the registration tables to regiastro, assuming that the tables are ordered according to the need. When it finds situation that satisfies the condition between the tables it advances a record in both. If in the new comparison the registers are not the same the programming will advance the record of lower value. When "OK" add register in "D3" D1 D2 A x A - OK (Add Register in D3, Move next D1 and D2) A x B - NOK Move next D1 B X B - OK (Add Register in D3, Move next D1 and D2) C X C - OK (Add Register in D3, Move next D1 and D2) C X C - OK (Add Register in D3, Move next D1 and D2 E X D - Move next D2 E X E - OK (Add Register in D3, Move next D1 and D2) E X F - Move next D1 F x F In the example used, the balance line will consider records that have the same information in both tables.

Is there any Proc that does this type of treatment?

Attachament: Balanceline VBA CODE example.

Muito Obrigado!

[Ksharp]

Honestly, I don't follow your question, 

Can you try to give us an example to illustrate it more ?

data have;
input FIELD1 $ FIELD2 $;
cards;
A   A
A   B
B   C
C   C
C   D
E   E
E   F
F   G
;
data want;
 set have;
 if FIELD1>FIELD2 then baseline=FIELD2;
  else baseline=FIELD1;
run;

[DiogoSilvaBR]

Hi. First of all, thank you for your help. Unfortunately your answer does not contain a solution to the case. I was not clear in saying that the fields are from different tables. I updated the question and added an example in VBA. I'd be grateful if you could spend some more time. Hug!

[ballardw]

In your actual data are the values numeric or Character?

If they are numeric values this looks doable with the MIN function.

 data one;
   input x;
datalines;
1
1
2
3
3
5
5
6
;
run;
data two;
   input y;
datalines;
1
2
3
3
4
5
6
7
;
run;

data want;
   merge one two;
   result = min(x,y);
run;

Your merge would work with character values but the key would be, as above, do not name the variables the same. If the data sets already exist you can use a rename data set option RENAME to change one variable name prior to the merge.

 data one;
   input x $;
datalines;
A
A
B
C
C
E
E
F
;
run;
data two;
   input x $;
datalines;
A
B
C
C
D
E
F
G
;
run;

data want;
   merge 
      one 
      two (rename=(x=y))
   ;
run;

The question of whether the variables have character values is critical because "less than" when comparing characters is not always what people think the comparison should yield. Consider the following code and see if the result makes sense. Look in the LOG for the output.

data example;
   a='a';
   b='X';
   if a>b then put a= 'is > ' b=;
   if b>a then put b= 'is > ' a=;
run;

[DiogoSilvaBR]

Hi. First of all, thank you for your help. Unfortunately your answer does not contain a solution to the case. As I pointed out, this is not the use of merge. I updated the question by attaching an example to VBA. I would appreciate it if you could devote a little more of your time to helping me. Hug!

[ballardw]

---

@DiogoSilvaBR wrote:
Hi. First of all, thank you for your help. Unfortunately your answer does not contain a solution to the case. As I pointed out, this is not the use of merge. I updated the question by attaching an example to VBA. I would appreciate it if you could devote a little more of your time to helping me. Hug!

---

So which part of the VBA code actually does the balance line part? Remove anything not directly related. I see a bunch of code related to dates, which you did not mention in your question so all of that should be removed or explained in the question in much more detail. SAS has a number of functions involving date values that makes me suspect that corrigeData function wouldn't be needed because of built in functions like YEAR, MONTH and/or DAY.

If any of the code involves calling other functions or subroutines they also need to be defined.

Any part that involves reading external data should likely be removed as that would already be accomplished or you would not have SAS data sets to work with. And a general comment on the VBA code: There is not a single comment. I might guess that comments if included would be Spanish which helps me little but might help someone else and a complete lack should mean this is a trivial exercise.

The code also contains magic number of 1000000 in this block of code:

contar = contar + 1
If contar > 1000000 Then
    DoEvents
    contar = 1
    DoEvents
End If

why is that limit there?

Perhaps you can show step by step what comparisons and results are being used.

Also explain how 8 input records each from two data sets create 10 output records in you your example D3 above. This seemingly contradicts "two ordered tables that have some logic resulting in a match process that resembles "Merge" but without it being possible for a record to match with more than one record from the other table.". How do you get more records out then input if one record isn't being considered a "match" to more than one in another.