Desktop productivity for business analysts and programmers

calculation of two dependent variable

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

Grand Advisor
Posts: 10,210

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.

Grand Advisor
Posts: 17,338

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.

 

 ABCDEF
1abcdemonhts
20,0350090,010,0350090,010,9549907251
30,0314340,010,030020,009550,9154212592
40,0204470,010,0187180,0091540,8875493773
50,0274120,010,0243290,0088750,8543446834
60,0285890,010,0244250,0085430,8213764775
70,0225270,010,0185030,0082140,7946592926
80,0244580,010,0194360,0079470,7672770387
90,0264420,010,0202880,0076730,7393157848
100,0270450,010,0199950,0073930,7119275389
110,02760,010,0196490,0071190,68515900110
120,0290330,010,0198930,0068520,65841486811
130,0260820,010,0171730,0065840,63465781612
140,0211420,010,0134180,0063470,61489300513
150,0221110,010,0135960,0061490,5951482214

 

 

 

abcdemonhts
0,0350090,01=A2=B2=1-(C2+D2)1
0,0314340,01=E2*A3=B3*E2=E2-C3-D32
0,0204470,01=E3*A4=B4*E3=F3-D4-E43
......
......

 

Thanks in advance!

Grand Advisor
Posts: 17,338

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;

Grand Advisor
Posts: 10,210

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.

Ask a Question
Discussion stats
  • 7 replies
  • 104 views
  • 3 likes
  • 3 in conversation