proc tabulate!!!

[sam369]

Hi All,

I have small question but unable to find the sol how to do that. i have a dataset with id

Sid

s100

s110

s100+s110(these are same subject in both s100+s110)

s200

i need to show like this using proc tabulat

var                            s100                    s110                  s100+s110           s200                total

apple                         100                   200                       300                    150                    450

but i am getting

apple                       100                    200                   300                         150                      750

Thanks in advance

regards

Sam

[Reeza]

You probably have another variable there that you're using to fill in the cell values but you haven't indicated what that is so hard to help beyond the following

proc tabulate data=sashelp.class;

class sex age;

table sex, (age all='Total');

run;

[RichardinOz]

As i understand it you want the s100+s110 rows to be excluded from the total but included in the position indicated in the final table.  I don't think Tabulate is the answer.  Proc Report might work.

I would introduce a group variable, ="A" for all the rows you want included in the total, ="B" otherwise, and use that as a class variable in proc summary with descending.  Then throw away the sum values for "B" and overall, transpose the output, and order the columns as you want.

Richard in Oz

[Cynthia_sas · Accepted solution]

Hi:

  The actual data is going to make a big difference here. PROC TABULATE allows something called multi-label formats. So, consider the following scenario. SASHELP.CLASS has a total of 19 students, between the ages of 11 and 16. I want to categorize them two different ways:

11-14: cannot drive

15-16: drivers

11-12: pre-teens

13-16: teens

  So, essentially, I want to"double count" the ages. This is similar to what you want to do. In my data, I just have 1 variable called AGE. So I have just the 19 observations in the data. To get TABULATE to do the "double count" (or count an age in 2 categories but still allowing the grand total to show as 19), I have to use PROC FORMAT to make a "multi-label" format. Then, once I make the format, I have to use the format in TABULATE, by specifying the MLF option on my CLASS statement.

  As long as you have not actually put "extra" observations into your data for the "s100+s110" items, you can use PROC FORMAT in a way similar to what I did. You should be able to test the program below. It uses SASHELP.CLASS, which should be available on your system. You can see in my screenshot, how the "grand total" is still 19 and the rows add up correctly.

cynthia

proc format;

  value age_mult (multilabel  notsorted)

                 11-14 = 'cannot drive'

                 15-16 = 'driver'

                 11-12 = 'pre-teen'

                 13-16 = 'teen';

run;

ods html file='c:\temp\mlf.html';

title 'Use Multilabel Formats';

proc tabulate data=sashelp.class;

  class age / mlf order=formatted;

  class sex;

  table sex all='Total', age*n all='Total';

  format age age_mult.;

run;

ods html close;

[sam369]

Thanks to All!!!!!!!

And Thanks  Cynthia ...Perfect that is what exactly i want!!!!!!!

Learned new thing today!!!!

Regards

Sam