Repeating Calculations

[SASFun1]

Hi. I am trying to calculate a rate. I have the population (y) in a column and multiple other columns (x) that uses this column. I know the formula to calculate the rate but want to know the code to bypass the repetitive need to insert hundreds of calculated columns with the formula (x/y*100). How do I set up my DO Loop to create the additional columns for each x column for these calculated rates? Did my question make sense? Lol. And can I do this in Enterprise Guide or need to write a specific code? Thank you for reading!

[Quentin]

Can you post an example of your data (can be fake data), preferably as a DATA step with CARDS data? With just a few variables.  And then also show the dataset you would want to create?

It sounds like you could code this with arrays.  Or often it is better (and easier) to transpose the data to make it a "long" dataset rather than "wide."  After transposing it to long, you can typically code this sort of solution much more easily.

[SASFun1]

Hi Quentin,

Thanks for the reply! I uploaded an example in Excel. First tab is the data. Second tab is the desired additional calculated columns. Do you think I need to transpose this data to get the code to work?

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[SASFun1]

I am new. I apologize. Here is what was similar to my example in the Excel sheet. I'll work on creating that test code with the instructions you provided. Thanks ballardw. 🙂

Dataset

Month Year	Population	Variable 1	Variable 2	Variable 3
Jan 2022	20	1	0	1
Feb 2022	40	0	1	7
Mar 2022	60	2	8	3

Desired

Month Year	Population	Variable 1	Calc Rate 1	Variable 2	Calc Rate 2	Variable 3	Calc Rate 3
Jan 2022	20	1	1/20*100	0	0/20*100	1	1/20*100
Feb 2022	40	0	0/40*100	1	1/40*100	7	7/40*100
Mar 2022	60	2	2/60*100	8	8/60*100	3	3/60*100

[Reeza]

data want;
set have;

array _input_vars(*) variable1-variable3;
array _output_vars(3) rate1-rate3;

do i=1 to dim(_input_vars);
     _output_vars(i) = population*_input_vars(i)/100;
end;

run;

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

---

@SASFun1 wrote:

Hi Quentin,

Thanks for the reply! I uploaded an example in Excel. First tab is the data. Second tab is the desired additional calculated columns. Do you think I need to transpose this data to get the code to work?

---

[SASFun1]

Thanks so much Reeza! I'll test this code out with my data set.

[SASFun1]

I tested it out, and it did not work. I will check out the UCLA source you provided. 🙂

[Quentin]

If you can describe more of the problem (did you get errors, unexpected results?) people will be able to help you more. Or better yet, post sample data with your code and describe the problem.

[ballardw]

---

@SASFun1 wrote:

I tested it out, and it did not work. I will check out the UCLA source you provided. 🙂

---

Any time that you get unexpected output or errors then share the LOG including the code and all messages from that step. Copy the text from the log, open a text box on the forum using the </> and paste the text.

The text box is important to preserve formatting of the text so that the diagnostic information SAS often provides is in the correct place as the main message windows on this forum reformat text. Also the box separates the log information from discussion. Text because it is much easier to make minor corrections and paste a response then to try to deal with any image files.

[Quentin]

Hmm @Reeza's array approach looks good to me.  I changed the calculation slightly:

data have ;
  input MonthYear $8. Population Var1 Var2 Var3 ;
  cards ;
Jan 2022 20 1 0 1
Feb 2022 40	0 1 7
Mar 2022 60	2 8 3
;

data want;
  set have;

  array _input_vars(*) var1-var3;
  array _output_vars(3) rate1-rate3;

  do i=1 to dim(_input_vars);
    _output_vars(i) =_input_vars(i)/population*100;
  end;

  drop i ;
run;

Or if you want to try the transpose to long approach I mentioned, something like:

proc sort data=have ;
  by MonthYear Population;
run ;

proc transpose data=have out=vert ;
  by MonthYear Population;
run ;

data want ;
  set vert ;
  rate=Col1/Population*100 ;
run ;

[SASFun1]

@Quentin You are so kind for all your msgs and code! I did not know I would get such support asking on here. 🙂 I will test out your code on Monday and let you know. Have a great weekend!

[SASFun1]

Hope you had a great weekend. I tried your top code - no errors and calculated correctly. But it only calculated Jan 2022's numbers. I'm thinking I need to incorporate DO loops? Second code with transposing would still need repetitive coding for the other rates. Thank you though!

[Quentin · Accepted solution]

Hi,

I'm not sure what you mean.  My top code (which is Really @Reeza's code with a small edit):

data have ;
  input MonthYear $8. Population Var1 Var2 Var3 ;
  cards ;
Jan 2022 20 1 0 1
Feb 2022 40	0 1 7
Mar 2022 60	2 8 3
;

data want;
  set have;

  array _input_vars(*) var1-var3;
  array _output_vars(3) rate1-rate3;

  do i=1 to dim(_input_vars);
    _output_vars(i) =_input_vars(i)/population*100;
  end;

  drop i ;
run;

proc print data=want ;
run ;

Looks like it generates your DESIRED dataset, with data for all three months:

        Month
Obs      Year      Population    Var1    Var2    Var3     rate1      rate2     rate3

 1     Jan 2022        20          1       0       1     5.00000     0.0000      5.0
 2     Feb 2022        40          0       1       7     0.00000     2.5000     17.5
 3     Mar 2022        60          2       8       3     3.33333    13.3333      5.0

I must  be misunderstanding something.  

[SASFun1]

No, @Reeza's code with the small edit by you works perfectly at my end now. I was in Enterprise Guide, not SAS 9.4 (face palm) I told you guys I am new to this 🙂

Now, I need to figure out how to incorporate this with my extremely large dataset. It said I submitted a code that contains lines longer than 6000 characters and aborted the submission lol. I broke it up by year and lower variables (I have up to 200+ and working with 5 years' worth of monthly data).

Thank you both very much. Much appreciated for the code to work off from!