Construct a summary table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

Thanks in advance!


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;

View solution in original post


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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 259 views
  • 4 likes
  • 3 in conversation