topic How to count divisions of multiple rows etc. in SAS Programming

How to count divisions of multiple rows etc.

Milla — Mon, 03 Feb 2014 06:37:37 GMT

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!

Re: How to count divisions of multiple rows etc.

LinusH — Mon, 03 Feb 2014 10:05:24 GMT

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.

Re: How to count divisions of multiple rows etc.

DBailey — Tue, 04 Feb 2014 13:23:22 GMT

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;

Re: How to count divisions of multiple rows etc.

Milla — Wed, 05 Feb 2014 07:14:10 GMT

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

Re: How to count divisions of multiple rows etc.

DBailey — Wed, 05 Feb 2014 13:55:25 GMT

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;

Re: How to count divisions of multiple rows etc.

Milla — Thu, 06 Feb 2014 08:28:39 GMT

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?

Re: How to count divisions of multiple rows etc.

DBailey — Thu, 06 Feb 2014 13:45:51 GMT

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;

Re: How to count divisions of multiple rows etc.

Milla — Thu, 20 Feb 2014 10:49:21 GMT

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.