topic Re: summarize with subcolumns in SAS Procedures

summarize with subcolumns

jcis7 — Tue, 21 Feb 2017 19:39:32 GMT

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!

Re: summarize with subcolumns

Reeza — Tue, 21 Feb 2017 20:37:31 GMT

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.

Re: summarize with subcolumns

Ksharp — Wed, 22 Feb 2017 02:25:08 GMT

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;

Re: summarize with subcolumns

Ksharp — Thu, 23 Feb 2017 02:11:59 GMT

@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.

Re: summarize with subcolumns

jcis7 — Mon, 27 Feb 2017 16:00:28 GMT

Thank you!

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

Re: summarize with subcolumns

Ksharp — Tue, 28 Feb 2017 02:11:11 GMT

Sure.

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

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

..........

ods excel close;