Select maximum variable

[Lupacante]

Hi all,

I have a dataset with column headings as follows:

account number | var1 | val1 | var2 | val2 | var3 | val3 | var4 | val4 | var5 | val5 | var6 | val6 |

this is what I would like to do:

if var1 = 1 then balance = val1;

if var2 = 1 then balance = val2;

if var3 = 1 then balance = val3;

...

if var6 = 1 then balance = val6;

However, for some account numbers, multiple vars are equal to one. I might have this situation:

account number | var1 | val1 | var2 | val2 | var3 | val3 | var4 | val4 | var5 | val5 | var6 | val6 |

123456              | 1     | 512 | 1     | 625 |       |        |        |       |        |        |        |       |

In this case, I would like to take make 'balance' equal to the highest 'val' there is. So as var1 and var2 are both equal to 1, I would like balance to equal the highest value of the relative vals. In this case, 625.

Any ideas?

thanks in advance,

Marco

[Ksharp]

Use your original code :

if var1 = 1 then balance = val1;

if var2 = 1 then balance = val2;

if var3 = 1 then balance = val3;

...

if var6 = 1 then balance = val6;

Xia Keshan

[RW9 · Accepted solution]

I think that wouldn't work with those if statements as you would set balance to the last non-missing value.  What you want is:

if var1 = 1 and val1 > balance then balance = val1;

...

if var6 = 1 and val6 > balance then balance = val6;

[RW9]

Assuming that 1 only appears if a value is present, try max function:

max(val1-val6).

In case you do not know how many variables then query the sashelp tables.

This will return the largest number from val1-6.

[data_null__]

RW9 wrote:

Assuming that 1 only appears if a value is present, try max function:
max(val1-val6).
In case you do not know how many variables then query the sashelp tables.
This will return the largest number from val1-6.

You may want to test this.

Your usage of (VAL1 - VAL6) is an expression not a "SAS Variable List".

[RW9]

Hi,

Yes, you are totally correct data _null_;.  The syntax is:

max(of val1-val6);

[Kurt_Bremser]

Since I hate to write the same code repeatedly, I've put in a macro.

%macro work;

%do i = 1 %to 6;

if var&i = 1

then balance = max(balance, val&i);

%end;

%mend;

data want;

set have;

balance = 0;

%work;

run;

[RW9]

Or, without macro:

data _null_;

     call execute('data want; set have;');

     do I=1 to 6;

          call execute('if var'||put(i,1.)||'=1 and val'||put(I,1.)||' > balance then balance=val'||put(I,1.)||';');

     end;

     call execute('run;');

run;

[Lupacante]

I ended up doing it like this:

if var1 ne 1 then val1 = '';

if var6 ne 1 then val2 = '';

if var6 ne 1 then val3 = '';

...

if var6 ne 1 then val6 = '';

balance = max(of val1-val6);

thanks everyone for your help!

Marco

[Kurt_Bremser]

But keep in mind that now you modify the values of var1 ... var6 before writing.

[Lupacante]

True, I do modify val1-val6, but I only need them in this step.