proc tabulate with age categories and mean age per year for overlapping cohorts

[TobbeNord]

Hi 

My problem is following.

I want this table 

 Per age category

Year	18-24 year	25-34 year	35-44 year	45-59 year	Mean
1991
1992
1993
1994
and so on
2016

But for each year I only want those who are between 18 - 59 years old, so I have created this format

%macro form();
proc format;
value $fodkoh (multilabel)
%do i=1932 %to 1957;
&i.-%eval(&i.+40)="%eval(&i.+59)"
%end;
;

%mend form;

The table works fine like this 

proc tabulate data=kohortinv;
class fodelsear/mlf;
table (fodelsear='' all='Alla'),all=' '*(N='Antal' Pctn<fodelsear all>='%'*f=4.1);
format fodelsear $fodkoh.;
run;

Then I get the total for each year, but how do I divide into age categories and mean for each year?

A person is included in several rows and also change age for each year.

(Using SAS 9.4 TS level 1M3)

[RW9]

Am seriously going to have to get this text as an auto-reply.  Please post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

There is no other way we can tell what you are working with.

I do these tables a fair bit, and the way I would do it is:

Create an age group variable in the data (if you work with clinical data you should be doing this as part of your reporting anyways).

proc means the data by year

proc freq (I assume you want counts in the age groups?) the data by age group

merge these two by year
proc transpose the result by year

[ballardw]

---

@TobbeNord wrote:

Hi 

My problem is following.

I want this table 

 Per age category

Year	18-24 year	25-34 year	35-44 year	45-59 year	Mean
1991
1992
1993
1994
and so on
2016

But for each year I only want those who are between 18 - 59 years old, so I have created this format

%macro form();
proc format;
value $fodkoh (multilabel)
%do i=1932 %to 1957;
&i.-%eval(&i.+40)="%eval(&i.+59)"
%end;
;

%mend form;

The table works fine like this 

proc tabulate data=kohortinv;
class fodelsear/mlf;
table (fodelsear='' all='Alla'),all=' '*(N='Antal' Pctn<fodelsear all>='%'*f=4.1);
format fodelsear $fodkoh.;
run;

Then I get the total for each year, but how do I divide into age categories and mean for each year?

A person is included in several rows and also change age for each year.

(Using SAS 9.4 TS level 1M3)

---

For proc tabulate to generate a column heading like that you will need a categorical variable. To calculate a MEAN you need a variable declared as a VAR variable to request the mean for. You cannot have a variable in proc tabulate as both a CLASS variable and a VAR variable.

But it is not clear whether your "mean" is supposed to be a mean age or mean of the percentages. Possibly something similar to

data want;
   set sashelp.class;
   age2=age;
run;

proc format library=work;
  value tabage
  10-13= '10-13'
  14-16= '14-16'
  ;
run;
proc tabulate data=want;
   class age sex;
   format age tabage.;
   var age2;
   table sex,
         age=''*pctn='%'
         age2=''*mean
   ;
run;

Where I am using the SEX in the role of year since I do not have your actual data. You should have the SASHELP.CLASS data set available to test this code and examine to see if the result is similar to your need.