BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sam369
Obsidian | Level 7

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


1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
Reeza
Super User

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
Quartz | Level 8

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
SAS Super FREQ

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
sam369
Obsidian | Level 7

Thanks to All!!!!!!!

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

Learned new thing today!!!!

Regards

Sam

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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