Help using Base SAS procedures

proc tabulate!!!

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

proc tabulate!!!

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



Accepted Solutions
Solution
‎11-21-2012 06:01 PM
SAS Super FREQ
Posts: 8,869

Re: proc tabulate!!!

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;


mlf_student.png

View solution in original post


All Replies
Super User
Posts: 19,878

Re: proc tabulate!!!

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;

Super Contributor
Posts: 644

Re: proc tabulate!!!

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

Solution
‎11-21-2012 06:01 PM
SAS Super FREQ
Posts: 8,869

Re: proc tabulate!!!

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;


mlf_student.png
Regular Contributor
Posts: 168

Re: proc tabulate!!!

Posted in reply to Cynthia_sas

Thanks to All!!!!!!!

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

Learned new thing today!!!!

Regards

Sam

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 316 views
  • 6 likes
  • 4 in conversation