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

Hi All,

 

I have the below data set

 

data test;

input Accounts $ TOB A_1 A_2 A_3 A_4 A_5 MOB_1 MOB_2 MOB_3 MOB_4 MOB_5 MOB_6 MOB_7 MOB_8 MOB_9 MOB_10;

cards;

A1 3 0.01 0.012 0.013 0.0147 0.0162 0.55556 0.77778 1 1.22222 1.44444 1.44444 1.44444 1.44444 1.44444 1.44444

A2 2 0.012 0.0122 0.0124 0.0126 0.0128 0.9375 0.96875 1 1.03125 1.0625 1.0625 1.0625 1.0625 1.0625 1.0625

A3 4 0.013 0.016 0.019 0.022 0.025 0.77778 0.88889 1 1.11111 1.22222 1.22222 1.22222 1.22222 1.22222 1.22222

A4 1 0.014 0.0143 0.0146 0.0149 0.0152 0.97561 0.9878 1 1.0122 1.02439 1.02439 1.02439 1.02439 1.02439 1.02439

;

run;

 

I need to multiply information based on the second column which is TOB, if TOB is 3 then resulting table should start multiplying the A_1 to MoB_3 and after that A_2 with A_4 and so on.

 

Another example for Account A2 has TOB is 2 then A_1 multiply with MoB_2, A2*Mob_3 and so on.

 

For more detail follow the attached excel file

 

Thank you so much in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

Not sure if this is what you have in mind:

 

data test;

input Accounts $ TOB A_1 A_2 A_3 A_4 A_5 MOB_1 MOB_2 MOB_3 MOB_4 MOB_5 MOB_6 MOB_7 MOB_8 MOB_9 MOB_10;

cards;
A1 3 0.01 0.012 0.013 0.0147 0.0162 0.55556 0.77778 1 1.22222 1.44444 1.44444 1.44444 1.44444 1.44444 1.44444
A2 2 0.012 0.0122 0.0124 0.0126 0.0128 0.9375 0.96875 1 1.03125 1.0625 1.0625 1.0625 1.0625 1.0625 1.0625
A3 4 0.013 0.016 0.019 0.022 0.025 0.77778 0.88889 1 1.11111 1.22222 1.22222 1.22222 1.22222 1.22222 1.22222
A4 1 0.014 0.0143 0.0146 0.0149 0.0152 0.97561 0.9878 1 1.0122 1.02439 1.02439 1.02439 1.02439 1.02439 1.02439
;
run;

data want;
set test;
array am am1-am5;
array a_ a_:;
array mob_ mob_:;
do over am;
am=a_*mob_(_i_+tob-1);
end;
run;

View solution in original post

3 REPLIES 3
Haikuo
Onyx | Level 15

Not sure if this is what you have in mind:

 

data test;

input Accounts $ TOB A_1 A_2 A_3 A_4 A_5 MOB_1 MOB_2 MOB_3 MOB_4 MOB_5 MOB_6 MOB_7 MOB_8 MOB_9 MOB_10;

cards;
A1 3 0.01 0.012 0.013 0.0147 0.0162 0.55556 0.77778 1 1.22222 1.44444 1.44444 1.44444 1.44444 1.44444 1.44444
A2 2 0.012 0.0122 0.0124 0.0126 0.0128 0.9375 0.96875 1 1.03125 1.0625 1.0625 1.0625 1.0625 1.0625 1.0625
A3 4 0.013 0.016 0.019 0.022 0.025 0.77778 0.88889 1 1.11111 1.22222 1.22222 1.22222 1.22222 1.22222 1.22222
A4 1 0.014 0.0143 0.0146 0.0149 0.0152 0.97561 0.9878 1 1.0122 1.02439 1.02439 1.02439 1.02439 1.02439 1.02439
;
run;

data want;
set test;
array am am1-am5;
array a_ a_:;
array mob_ mob_:;
do over am;
am=a_*mob_(_i_+tob-1);
end;
run;
devsas
Pyrite | Level 9

Haikyo, This was very helpful from my learning perspective. Thanks. I'm little unsure about 

array a_ a_:;
array mob_ mob_:;
am=a_*mob_(_i_+tob-1);

What does these 3 line codes mean and during what kind of situations u use this?  

ballardw
Super User

These two lines assign all of the variables whose names start with a_ or mob_ to the array. The : immediately after characters of a variable name create a variable list that includes all those variables. Basically short hand so you don't have to write out 5, 10 or 100 variables.

array a_ a_:;
array mob_ mob_:;

 

The DO loop that you omit repeat calculations for each of the variables in the AM array. There is basically an unstated array index, _i_, that would match things up. Since the MOB_ array want to use different values for the calculation other than the matching index number the value calculated inside the parantheses is used to "match up" the desired MOB_ varaibel with A_.

 

am=a_*mob_(_i_+tob-1);

 

another way to write things explicitly (and the possible confusion is one reason SAS no long recommends the Do Over construct)

do _i_ = 1 to dim(am);

   am(_i_)=a_(_i_) * mob_ (_i_+tob-1)

end;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1677 views
  • 2 likes
  • 4 in conversation