Construct a summary table

Solved
Occasional Contributor
Posts: 8

Construct a summary table

Hi,

I have a sample dataset below:

Id gender year  numeric_varx

1    F      2009

1    F      2010

1    F      2011

1    F      2012

2    M     2009

2    M     2010

2    M     2011

2    M     2012

I need to find the mean and standard deviation of the variable numeric_varx separately for each year and gender.

I have tried to use the following command:

proc means n mean std maxdec=2 data=x;

var numeric_varx;

by gender year;

title 'Summary statistics for varx between 2009 and 2012';

run;

And I had an error message saying:

ERROR: Data set WORK.X is not sorted in ascending sequence. The current BY group has Study

year = 2012 and the next BY group has Study year = 2009.

Then I realised "proc sort" might help in this case. Then I add the 'proc sort" command back before "proc means".

proc sort data=x;

by gender year;

But there are twelve separated tables by gender and year.

Besides, there are some missing gender details shown. (ie Gender=. & Year=2009)

But in fact it should not have missing value for gender~

Why would that happen? Did I use the wrong command?

Also,is it possible to make it as ONE neat table instead as follows? :smileyconfused:

Summary statistics for varx between 2009 and 2012

Males                       Females

N     Mean      S.D     N     Mean     S.D.

2009

2010

2011

2012

Accepted Solutions
Solution
‎06-14-2013 08:15 AM
Regular Contributor
Posts: 151

Re: Construct a summary table

You haven't included any values for numeric_varx in your sample data. Also you must be running against more data to that posted as there is only 1 record for each combination of gender and year and therefore it should return 8 tables.

If you put gender and year in a CLASS statement instead of a BY statement then this will avoid the need to pre-sort the data and will produce only 1 table with the breakdown you want.

As @shmilycn said, in order to get the layout you want, use PROC TABULATE, something like below.

proc tabulate data=x;

class gender year;

var numeric_varx;

table year='', gender='' * numeric_varx='' * (n='N' mean='Mean' std='S.D') / box='Year';

run;

All Replies
New Contributor
Posts: 2

Re: Construct a summary table

using proc tabulate may help.

Solution
‎06-14-2013 08:15 AM
Regular Contributor
Posts: 151

Re: Construct a summary table

You haven't included any values for numeric_varx in your sample data. Also you must be running against more data to that posted as there is only 1 record for each combination of gender and year and therefore it should return 8 tables.

If you put gender and year in a CLASS statement instead of a BY statement then this will avoid the need to pre-sort the data and will produce only 1 table with the breakdown you want.

As @shmilycn said, in order to get the layout you want, use PROC TABULATE, something like below.

proc tabulate data=x;

class gender year;

var numeric_varx;

table year='', gender='' * numeric_varx='' * (n='N' mean='Mean' std='S.D') / box='Year';

run;

🔒 This topic is solved and locked.