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.
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.
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?
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.
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 |
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?
Thanks so much Reeza! I'll test this code out with my data set.
I tested it out, and it did not work. I will check out the UCLA source you provided. 🙂
@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.
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 ;
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!
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.
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!
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!
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.