BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASFun1
Fluorite | Level 6
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!
1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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.  

The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.

View solution in original post

15 REPLIES 15
Quentin
Super User

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.

The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.
SASFun1
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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 YearPopulationVariable 1Variable 2Variable 3
Jan 2022

20

101
Feb 202240017
Mar 202260283

 

Desired

Month YearPopulationVariable 1Calc Rate 1Variable 2Calc Rate 2Variable 3Calc Rate 3
Jan 2022

20

11/20*100 00/20*10011/20*100
Feb 20224000/40*10011/40*10077/40*100
Mar 20226022/60*10088/60*10033/60*100
Reeza
Super User
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
Fluorite | Level 6

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

SASFun1
Fluorite | Level 6

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

Quentin
Super User
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.
The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.
ballardw
Super User

@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
Super User

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 ;

 

The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.
SASFun1
Fluorite | Level 6
@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
Fluorite | Level 6

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
Super User

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.  

The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.
SASFun1
Fluorite | Level 6

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! 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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.

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
  • 15 replies
  • 2568 views
  • 3 likes
  • 5 in conversation