BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lupacante
Calcite | Level 5

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


1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

10 REPLIES 10
Ksharp
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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__
Jade | Level 19

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
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

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

max(of val1-val6);

Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

Lupacante
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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