Desktop productivity for business analysts and programmers

Select maximum variable

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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?

thanks in advance,

Marco



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

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;

View solution in original post


All Replies
Super User
Posts: 9,874

Re: Select maximum variable

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

Solution
‎04-14-2014 08:13 AM
Super User
Super User
Posts: 7,720

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
Super User
Posts: 7,720

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.

Respected Advisor
Posts: 3,788

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
Super User
Posts: 7,720

Re: Select maximum variable

Hi,

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

max(of val1-val6);

Super User
Posts: 7,444

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
Super User
Super User
Posts: 7,720

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

thanks everyone for your help!

Marco

Super User
Posts: 7,444

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
Contributor
Posts: 27

Re: Select maximum variable

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 402 views
  • 0 likes
  • 5 in conversation