topic Re: Select maximum variable in SAS Enterprise Guide

Select maximum variable

Lupacante — Mon, 14 Apr 2014 11:27:31 GMT

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

Re: Select maximum variable

Ksharp — Mon, 14 Apr 2014 12:02:52 GMT

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

Re: Select maximum variable

RW9 — Mon, 14 Apr 2014 12:12:19 GMT

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.

Re: Select maximum variable

Kurt_Bremser — Mon, 14 Apr 2014 12:13:44 GMT

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;

Re: Select maximum variable

RW9 — Mon, 14 Apr 2014 12:13:48 GMT

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;

Re: Select maximum variable

RW9 — Mon, 14 Apr 2014 12:16:01 GMT

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;

Re: Select maximum variable

data_null__ — Mon, 14 Apr 2014 13:35:59 GMT

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

Re: Select maximum variable

Lupacante — Mon, 14 Apr 2014 13:44:44 GMT

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

Re: Select maximum variable

RW9 — Mon, 14 Apr 2014 13:45:37 GMT

Hi,

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

max(of val1-val6);

Re: Select maximum variable

Kurt_Bremser — Mon, 14 Apr 2014 13:50:59 GMT

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

Re: Select maximum variable

Lupacante — Mon, 14 Apr 2014 13:53:11 GMT

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