turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How to count divisions of multiple rows etc.

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-03-2014 01:37 AM

I have a data like

Year Age x1 x2

1 1 v11 v12

2 1 v21 v22

...

21 1 v211 v212

22 1 v221 v222

....

The last year is 133 and then the age 2 starts (goes through the same years) until the highest age 106.

I need to calculate a new variable of the variables x1 and x2. That is the sum of the years 1,2,3,4,5 and 2,3,4,5,6 and so on in x1 (in each age separately) divided By the sum of the same calculation in x2.

Year Age x1 x2 x3

1 1 v11 v12 (v11+v21+v31+v41+v51)/(v12+v22+v32+v42+v52)

2 1 v21 v22 (v21+v31+v41+v51+v61)/(v22+v32+v42+v52+v62)

...

21 1 v211 v212 (v211+v221+v231+v241+v251)/(v212+v222+v232+v242+v252)

22 1 v221 v222 (v221+v231+v241+v251+v261)/(v222+v232+v242+v252+v262)

After that I need another calculation of for example the value in x3 in year 1 divided By the value in x3 in year 21, the natural logarith of those divided By the amount of years between them (20).

1/20*ln(x3(year1)/x3(year21)) and so fort that in each case there is the 20 year difference. That calculation can be x4 and it should be placed according the latter year.

I hope you could understand what i meant!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-03-2014 05:05 AM

Requirement is quite straightforward by using SQL;

select age, sum(x1) / sum(x2) as mean

from have

group by year

;

If you really want this information on each detail row, you just add Year to the SQL and it will automatically remerge your calculation on the detail rows.

On the LOG issue, please show some sample desired output to help us understand better.

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-04-2014 08:23 AM

I think that approach doesn't meet the stated objectives of 5 year increments.

proc sql;

create table work.want as

select

t1.year,

t1.age,

t1.x1,

t1.x2,

sum(t2.x1)/sum(t2.x2) as x3

from

have t1

left join have t2

on t1.age=t2.age

and t1.year ge t2.year

and t1.year+5 lt t2.year

group by

t1.year,

t1.age,

t1.x1,

t1.x2;

create table want2 as

select

t1.*,

1/20*ln(t2.x3/t1.x3) as x4

from

want t1

left join want t2

on t1.age=t2.age

and t1.year-20=t2.year;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-05-2014 02:14 AM

I don't get it, the x3 and x4 have just missing values in the produced table.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-05-2014 08:55 AM

try this one to get the first table:

proc sql;

create table work.want as

select

t1.year,

t1.age,

t1.x1,

t1.x2,

sum(t2.x1) as t2x1,

sum(t2.x2) as t2x2,

sum(t2.x1)/sum(t2.x2) as x3

from

have t1

left join have t2

on t1.age=t2.age

and t2.year between t1.year and (t1.year+4)

group by

t1.year,

t1.age,

t1.x1,

t1.x2;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-06-2014 03:28 AM

Thank you so much! It's working now. Can you tell me one more thing. In case I wanted to have the x4's placed in the first year, what should be changed?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-06-2014 08:45 AM

I think all you would have to do is change the join:

proc sql;

create table want2 as

select

t1.*,

1/20*ln(t2.x3/t1.x3) as x4

from

want t1

left join want t2

on t1.age=t2.age

and t1.year=t2.year-20;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-20-2014 05:49 AM

Thanks! Hey, one more thing came up. I need a new variable that is x3 but lagged two years. That's to say, the value of x3 in year 1 would be in year 3. I can't use plain lag function because it interrupts the order of later ages after the 1st age. So basically the first two years in each age should be missing. I'd rather have a new variable and not mess the original x3 since I will need both.