BookmarkSubscribeRSS Feed
Edita_ba
Fluorite | Level 6

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.

7 REPLIES 7
ballardw
Super User

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
Super User

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
Fluorite | Level 6

 

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!

Reeza
Super User

Look at the LAG function to use the previous value. 

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

 

 

Edita_ba
Fluorite | Level 6

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
Fluorite | Level 6

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

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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