calculation of two dependent variable

[Edita_ba]

Hi,

does the enyone have idea how to calculate two dependent variable, in concrete cases how to calculate columns c,d and e. Example is in excel attachment.

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

If you have a SAS data set for an example you can post data step code to describe the data. 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 {i} icon or attached as text to show exactly what you have and that we can test code against.

[Reeza]

1. Please post the data directly into the forum. Many users will not download attachments.

2. Please describe your problem in more detail. I don't want to have to read through all the formula's in an excel spreadsheet to try and figure out what you want. 

3. Show what you've tried.

4. You've posted this under EG, are you using the GUI or programming.

[Edita_ba]

Thanks for your comments and sugestion as wll. So, I will try to explain my problem:

-columns a and b are known;

-columns c,d and e have to be calculated on folowing way:

   - for month 1: c2=a2; d2=b2; e2=1-(c2+d2)

   - for month 2:c3=E2*A3;d3=B3*E2; e3=E2-C3-D3

  - for month 3: c4=E3*A4;d4=B4*E3;e=F3-D4-E4 etc.

 Below are calculated values in excel.

	A	B	C	D	E	F
1	a	b	c	d	e	monhts
2	0,035009	0,01	0,035009	0,01	0,954990725	1
3	0,031434	0,01	0,03002	0,00955	0,915421259	2
4	0,020447	0,01	0,018718	0,009154	0,887549377	3
5	0,027412	0,01	0,024329	0,008875	0,854344683	4
6	0,028589	0,01	0,024425	0,008543	0,821376477	5
7	0,022527	0,01	0,018503	0,008214	0,794659292	6
8	0,024458	0,01	0,019436	0,007947	0,767277038	7
9	0,026442	0,01	0,020288	0,007673	0,739315784	8
10	0,027045	0,01	0,019995	0,007393	0,711927538	9
11	0,0276	0,01	0,019649	0,007119	0,685159001	10
12	0,029033	0,01	0,019893	0,006852	0,658414868	11
13	0,026082	0,01	0,017173	0,006584	0,634657816	12
14	0,021142	0,01	0,013418	0,006347	0,614893005	13
15	0,022111	0,01	0,013596	0,006149	0,59514822	14

a	b	c	d	e	monhts
0,035009	0,01	=A2	=B2	=1-(C2+D2)	1
0,031434	0,01	=E2*A3	=B3*E2	=E2-C3-D3	2
0,020447	0,01	=E3*A4	=B4*E3	=F3-D4-E4	3
.	.	.	.	.	.
.	.	.	.	.	.

Thanks in advance!

[Reeza]

Look at the LAG function to use the previous value. 

You didn't answer if you're programming or using the GUI.

[Edita_ba]

Hi,

Yes,I'm programing. I try with lag function but without sucess.

Do you mean I need lag for each step (row) calculation?

[Edita_ba]

in this case I have consecutive calculation between t and t-1 row;

[ballardw]

---

@Edita_ba wrote:

Hi,

Yes,I'm programing. I try with lag function but without sucess.

Do you mean I need lag for each step (row) calculation?

---

Please show some example code of what you attempted and what "without success" means for that code. If you had errors or warning messages post the log with the code and messages into a code box opened with the forum {i} icon. Errors often have positional indicators that will often not appear in the main forum windows as the forum seems to strip leading blanks, tabs and sometimes white space in the middle of lines.

If the result is not expected show what the results were and what was expected (values not formula).

Note that use of LAG or DIF functions with in conditionals such as: If Lag(var) > 3 ... is an extremely complicated issue and it is much better to have LAG calls outside of any conditional code structures creating temporary variables that are dropped from the resulting set after the logic is worked out.