summarize with subcolumns

[jcis7]

Hi,

I have data that looks like this:

builidingname    vax1  vax2    vax3     buildingtype

happy days           1        2          4        private

learning tree          2       1          3        public

bean stalk              1      1          2        public

How do I summarize the info for all buildings as well as by buildingtype so I get the data like this:

      vax1                             vax2                            vax3

all   public private          all   public private       all   public private 

4       3         1               4         2        2             9      5         4

Thanks!

[Reeza]

Is this a report or a dataset that you need? I'm guesing report based on the structure.

Have you looked at proc report or tabulate? If so, what does your code look like so far?

I would probably recommend proc tabulate.

[Ksharp]

data have;
input buildingname & $20.   vax1  vax2    vax3     buildingtype $;
cards;
happy days           1        2          4        private
learning tree          2       1          3        public
bean stalk              1      1          2        public
;
run;
proc transpose data=have out=want;
by buildingname buildingtype notsorted;
var vax:;
run;
proc tabulate data=want;
 class _name_ buildingtype;
 var col1;
 table _name_=' '*(all buildingtype=' ')*col1=' '*sum=' '*f=best12.;
run;

[Ksharp · Accepted solution]

@mkeintz sent me a private message. And I quote here.

You don't need data set WANT.  This is actually a good case to use multiple analysis vars in the proc tabulate:

proc tabulate data=have noseps order=freq;

  class buildingtype;

  var vax1-vax3;

  tables sum=''*(vax1 vax2 vax3)*(all buildingtype='')*f=9.0;

run;

Using the "order=freq" is a bit sneaky since it won't reliably order the table as (All,Public,Private) although it does in this case, as per OP's sample output.

[jcis7]

Thank you!

Is it possible to output that into MS Excel instead of keeping it is SAS as HTML?

[Ksharp]

Sure.

ods excel file='c:\temp\x.xlsx';

proc tabulate............

..........

ods excel close;