Solved
Contributor
Posts: 27

# Select maximum variable

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?

Marco

Accepted Solutions
Solution
‎04-14-2014 08:13 AM
Super User
Posts: 9,829

## Re: Select maximum variable

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;

All Replies
Super User
Posts: 10,849

## Re: Select maximum variable

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

Solution
‎04-14-2014 08:13 AM
Super User
Posts: 9,829

## Re: Select maximum variable

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;

Super User
Posts: 9,829

## Re: Select maximum variable

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.

Posts: 3,867

## Re: Select maximum variable

```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".

Super User
Posts: 9,829

## Re: Select maximum variable

Hi,

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

max(of val1-val6);

Super User
Posts: 10,557

## Re: Select maximum variable

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,829

## Re: Select maximum variable

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;

Contributor
Posts: 27

## Re: Select maximum variable

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);

Marco

Super User
Posts: 10,557

## Re: Select maximum variable

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

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