New Contributor
Posts: 4

# calculation of two dependent variable

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.

Super User
Posts: 13,941

## Re: calculation of two dependent variable

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.

Super User
Posts: 24,010

## Re: calculation of two dependent variable

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.

New Contributor
Posts: 4

## Re: calculation of two dependent variable

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

Super User
Posts: 24,010

## Re: calculation of two dependent variable

Look at the LAG function to use the previous value.

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

New Contributor
Posts: 4

## Re: calculation of two dependent variable

Hi,

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

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

New Contributor
Posts: 4

## Re: calculation of two dependent variable

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

Super User
Posts: 13,941

## Re: calculation of two dependent variable

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.

Discussion stats
• 7 replies
• 170 views
• 3 likes
• 3 in conversation