topic Re: Repeating Calculations in SAS Programming

Repeating Calculations

SASFun1 — Fri, 17 Mar 2023 14:04:47 GMT

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!

Re: Repeating Calculations

Quentin — Fri, 17 Mar 2023 14:21:05 GMT

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.

Re: Repeating Calculations

SASFun1 — Fri, 17 Mar 2023 14:31:25 GMT

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?

Re: Repeating Calculations

ballardw — Fri, 17 Mar 2023 14:43:59 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Repeating Calculations

Reeza — Fri, 17 Mar 2023 14:52:46 GMT

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?

Re: Repeating Calculations

SASFun1 — Fri, 17 Mar 2023 15:01:53 GMT

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

Re: Repeating Calculations

SASFun1 — Fri, 17 Mar 2023 15:03:36 GMT

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

Re: Repeating Calculations

SASFun1 — Fri, 17 Mar 2023 19:59:42 GMT

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

Re: Repeating Calculations

Quentin — Fri, 17 Mar 2023 20:02:56 GMT

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.

Re: Repeating Calculations

ballardw — Fri, 17 Mar 2023 21:51:15 GMT

@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.

Re: Repeating Calculations

Quentin — Fri, 17 Mar 2023 22:44:38 GMT

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 ;

Re: Repeating Calculations

SASFun1 — Fri, 17 Mar 2023 22:56:58 GMT

@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!

Re: Repeating Calculations

Season — Sat, 18 Mar 2023 01:43:18 GMT

Hello, I've read your question and found out that your problem is somehow similar with a problem I encountered a week ago. By then, I was trying to test the normality of a certain variable when grouped by multiple categorical variables. Since the CLASS statement can only put one variable in, I had to repeat PROC UNIVARIATE many times to do this job. The CALL EXECUTE routine perfectly solved my problem. Since your problem is also centered on repetitive work, I think that it might also work for you. See the code in the solution of How should I use SAS macro to perform PROC UNIVARIATE repetitively? for more details. The method was suggested to me by @ballardw. I later made some adjustments to the code and eventually formed the code in the solution. I hope it will be helpful to you.

Re: Repeating Calculations

SASFun1 — Mon, 20 Mar 2023 20:31:01 GMT

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!

Re: Repeating Calculations

Quentin — Mon, 20 Mar 2023 21:15:48 GMT

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.

Re: Repeating Calculations

SASFun1 — Mon, 20 Mar 2023 22:03:39 GMT

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!