Creating new variable from vertical sums.

[Julle]

Hi Guys!

I'm working on my master-thesis, but I've gotten stuck in the programming-stage. Could really use some help from fellow sas-programmers. Thanks in advance!

Basically what I'm trying to do is to write a program calculating the expected risks of small bowel cancer in terms of incidence per person, starting year in risk population, age when entering the riskpopulation and the total years spent in the population. These expected risks will later on be used to construct SIR's.

Varibles

-------------------------------------

year (range: 1970 to 2010)

gender (male,female)

age (range: 0 to 100)

incidence per person

years til event/end of follow up ( 1-41 for 1970, 1-40 for 1971 etc..)

---------------------------------------

thus,  resulting dataset has 200 000+ rows/kombinations

now what I want is to create a new variable called 'risk' which sums the incidences across rows.

Example:

Lets say a female enters the population in year 1975 at the age of 30 and spends a total of 3 years in the population.

The 'risk'-variable would then be:

Risk(female,1970,30,3)=incidence(female, 1975, 30)+ incidence(female, 1976, 31) + incidence(female, 1977, 32)

How can I write this?

[Astounding]

Julle,

This is a relatively easy problem.  PROC SUMMARY will do the job, and plenty of people will help you with it.  Here are a couple of tasks that fall to you.

First, there must be another variable, such as ID in your data set.  Otherwise, how could you tell which observations belong to the same person?

The final result will be a summarized data set with one observation per person.  So depending on the programming approach used, it probably will end up being a separate data set rather than having the summary statistics attached to every incoming observation.  If you have preferences about what the result should look like, let us know.

Finally, can we assume that every observation in the incoming data represents an incidence year?  If not, how do we know which years should be included in the summary and which ones omitted?

The programming is straightforward, something like this (depending on the answers to these questions):

proc summary data=have nway;

   class id;

   var year incidence;

   output out=stats (keep=id risk first_year last_year) sum(incidence)=risk min(year)=first_year max(year)=last_year;

run;

You would still have to use first_year and last_year to compute duration later.

Good luck.

[Julle]

Thanks a bunch for your reply, Astounding!