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
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;
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
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;
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.
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".
Hi,
Yes, you are totally correct data _null_;. The syntax is:
max(of val1-val6);
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;
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;
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
But keep in mind that now you modify the values of var1 ... var6 before writing.
True, I do modify val1-val6, but I only need them in this step.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.