Dynamically Calculate of Variables

[mlogan]

Hello,

I have tuition fees laid out for 4 years (Variables Fee_YR_01-Fee_YR_04) starting current year (Fee_YR_01). Variables Increase_YR_01-Increase_YR_04 in the sample dataset shows the proposed tuition fee increase rate (in percent) based on the previous year. I want to calculate the future tuition based on this proposed tuition increase rate (in the new variables Fee_New_YR_02 - Fee_New_YR_04). I want a dynamic solution for this calculation.  Thank you all.

My data set looks like this:

data have;
	input Fee_YR_01 Fee_YR_02 Fee_YR_03 Fee_YR_04 Increase_YR_01 Increase_YR_02 Increase_YR_03 Increase_YR_04;
datalines;
1000	1050	1100	1150	0	20	20	20
1000	1070	1100	1150	0	20	20	20
3000	3100	3150	3200	0	15	15	15
3000	3200	3200	3300	0	15	15	15
4000	4150	4200	4250	0	15	15	15
4000	4200	4300	4350	0	15	15	15

;
RUN;

This is the output I want:

Fee_New_YR_02	Fee_New_YR_03	Fee_New_YR_04
1200	1260	1320
1200	1284	1320
3450	3565	3622.5
3450	3680	3680
4600	4772.5	4830
4600	4830	4945

Calculations: Fee_New_YR_02=Fee_YR_01 * 1.2 (increased by 20%)

I have shown you a small set of data. But I have 150 variables for tuition fees and 150 variables for the tuition rate increase.  So I need help on how to do it dynamically. 

[mlogan]

Hello,

I have tuition fees laid out for 4 years (Variables Fee_YR_01-Fee_YR_04) starting current year (Fee_YR_01). Variables Increase_YR_01-Increase_YR_04 in the sample dataset shows the proposed tuition fee increase rate (in percent) based on the previous year. I want to calculate the future tuition based on this proposed tuition increase rate (in the new variables Fee_New_YR_02 - Fee_New_YR_04). I want a dynamic solution for this calculation.  Thank you all.

My data set looks like this:

data have;
	input Fee_YR_01 Fee_YR_02 Fee_YR_03 Fee_YR_04 Increase_YR_01 Increase_YR_02 Increase_YR_03 Increase_YR_04;
datalines;
1000	1050	1100	1150	0	20	20	20
1000	1070	1100	1150	0	20	20	20
3000	3100	3150	3200	0	15	15	15
3000	3200	3200	3300	0	15	15	15
4000	4150	4200	4250	0	15	15	15
4000	4200	4300	4350	0	15	15	15

;
RUN;

This is the output I want:

Fee_New_YR_02	Fee_New_YR_03	Fee_New_YR_04
1200	1260	1320
1200	1284	1320
3450	3565	3622.5
3450	3680	3680
4600	4772.5	4830
4600	4830	4945

Calculations: Fee_New_YR_02=Fee_YR_01 * 1.2 (increased by 20%)

I have shown you a small set of data. But I have 150 variables for tuition fees and 150 variables for the tuition rate increase.  So I need help on how to do it dynamically. 

[Reeza]

Please do not post the question in multiple locations. 

Update your dimensions and variable series accordingly and this should work:

data want;
set have;

array fee_hist (4) fee_yr_01 - fee_yr_04;
array increase(4) increase_yr_01 - increase_yr_04;
array fee_new (2:4) fee_new_yr_02 - fee_new_yr_04;

do i=2 to 4;
fee_new(i) = fee_hist(i-1) * (1+(increase(i)/100));
end;

run;

Or slightly more dynamically, update your end of the series:

data want;
set have;

array fee_hist (*) fee_yr_01 - fee_yr_04;
array increase(*) increase_yr_01 - increase_yr_04;
array fee_new (*) fee_new_yr_02 - fee_new_yr_04;

do i=2 to dim(fee_hist);
fee_new(i-1) = fee_hist(i-1) * (1+(increase(i)/100));
end;

run;

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

---

@mlogan wrote:

Hello,

I have tuition fees laid out for 4 years (Variables Fee_YR_01-Fee_YR_04) starting current year (Fee_YR_01). Variables Increase_YR_01-Increase_YR_04 in the sample dataset shows the proposed tuition fee increase rate (in percent) based on the previous year. I want to calculate the future tuition based on this proposed tuition increase rate (in the new variables Fee_New_YR_02 - Fee_New_YR_04). I want a dynamic solution for this calculation.  Thank you all.

My data set looks like this:

data have;
	input Fee_YR_01 Fee_YR_02 Fee_YR_03 Fee_YR_04 Increase_YR_01 Increase_YR_02 Increase_YR_03 Increase_YR_04;
datalines;
1000	1050	1100	1150	0	20	20	20
1000	1070	1100	1150	0	20	20	20
3000	3100	3150	3200	0	15	15	15
3000	3200	3200	3300	0	15	15	15
4000	4150	4200	4250	0	15	15	15
4000	4200	4300	4350	0	15	15	15

;
RUN;

This is the output I want:

Fee_New_YR_02	Fee_New_YR_03	Fee_New_YR_04
1200	1260	1320
1200	1284	1320
3450	3565	3622.5
3450	3680	3680
4600	4772.5	4830
4600	4830	4945

Calculations: Fee_New_YR_02=Fee_YR_01 * 1.2 (increased by 20%)

I have shown you a small set of data. But I have 150 variables for tuition fees and 150 variables for the tuition rate increase.  So I need help on how to do it dynamically. 

---

[mlogan]

Thank you Reeza. Your code worked for me. I just have slightly different requirements now and wondering if you can help.

I want the calculated output of the first row will be copied below all the way to the end.

I was able to do the calculation on the first line, but don't know how to copy downward. Thank you for your help.

data have;
	input Fee_YR_01 Fee_YR_02 Fee_YR_03 Fee_YR_04 Increase_YR_01 Increase_YR_02 Increase_YR_03 Increase_YR_04;
datalines;
1000	1050	1100	1150	0	20	20	20
1000	1070	1100	1150	0	20	20	20
3000	3100	3150	3200	0	15	15	15
3000	3200	3200	3300	0	15	15	15
;
RUN;

DATA Want;
SET  Have;

ARRAY Increase_Rate(*) Increase_YR_01 - Increase_YR_04;
ARRAY Fee_New(*)        Fee_New_AY_01 - Fee_New_AY_04;

Fee_New_AY_01=Fee_YR_01;
   DO i=2 TO dim(Fee_New);
   
	IF _n_=1 THEN fee_new(i) = FLOOR(( fee_new(i-1) * (1+(Increase_Rate(i)/100)) )/10)*10;
    
END;
DROP i;
RUN;

Expected Output:

Fee_YR_01	Fee_YR_02	Fee_YR_03	Fee_YR_04	Increase_YR_01	Increase_YR_02	Increase_YR_03	Increase_YR_04	Fee_New_AY_01	Fee_New_AY_02	Fee_New_AY_03	Fee_New_AY_04
1000	1050	1100	1150	0	20	20	20	1000	1200	1440	1720
1000	1070	1100	1150	0	20	20	20	1000	1200	1440	1720
3000	3100	3150	3200	0	15	15	15	3000	1200	1440	1720
3000	3200	3200	3300	0	15	15	15	3000	1200	1440	1720

[mlogan]

Hi all,

I have lat 4 year tuition fee laid out as Fee_YR_01-Fee_YR_04 and their corresponding increased rate Increase_YR_01-Increase_YR_04. I want 4 new rows which will calculate the tuition fee for the next 4 years based on their previous year tuitions and corresponding increase. But instead of calculating all 4 rows, I want the calculated output of the first row to be copied below all the way to the end.

I was able to do the calculation on the first row, but don't know how to copy downward. Can anyone help please. Thank you

data have;
	input Fee_YR_01 Fee_YR_02 Fee_YR_03 Fee_YR_04 Increase_YR_01 Increase_YR_02 Increase_YR_03 Increase_YR_04;
datalines;
1000	1050	1100	1150	0	20	20	20
1000	1070	1100	1150	0	20	20	20
3000	3100	3150	3200	0	15	15	15
3000	3200	3200	3300	0	15	15	15
;
RUN;

DATA Want;
SET  Have;

ARRAY Increase_Rate(*) Increase_YR_01 - Increase_YR_04;
ARRAY Fee_New(*)        Fee_New_AY_01 - Fee_New_AY_04;

Fee_New_AY_01=Fee_YR_01;
   DO i=2 TO dim(Fee_New);
   
	IF _n_=1 THEN fee_new(i) = FLOOR(( fee_new(i-1) * (1+(Increase_Rate(i)/100)) )/10)*10;
    
END;
DROP i;
RUN;

Expected Output:

Fee_YR_01	Fee_YR_02	Fee_YR_03	Fee_YR_04	Increase_YR_01	Increase_YR_02	Increase_YR_03	Increase_YR_04	Fee_New_AY_01	Fee_New_AY_02	Fee_New_AY_03	Fee_New_AY_04
1000	1050	1100	1150	0	20	20	20	1000	1200	1440	1720
1000	1070	1100	1150	0	20	20	20	1000	1200	1440	1720
3000	3100	3150	3200	0	15	15	15	3000	1200	1440	1720
3000	3200	3200	3300	0	15	15	15	3000	1200	1440	1720

[mkeintz · Accepted solution]

Look up the RETAIN statement, which tells SAS to avoid resetting new variables to missing values from iteration to iteration.  You apparently want to retain the values of the Fee_New_AY_01 - Fee_New_AY_04 variables calculated when _n_=1.

[mlogan]

Thank you mkeintz. It worked for me perfectly. Never used this RETAIN function before. Today I have learded something new.

[mlogan]

Hi mkeintz, Do you know how I can stop RETAIN function to copy the value all the way till the blank line? I want it to stop at one point when my another by value (sorted column) ends. 

Thanks. 

[andreas_lds]

I have merged your posts, please don't repost questions.