topic Pivot type reports in SAS Procedures

Pivot type reports

pearlmy — Mon, 19 Mar 2012 17:46:05 GMT

What is the easiest way to create pivot type reports?

Pivot type reports

PGStats — Mon, 19 Mar 2012 17:53:04 GMT

proc tabulate;

Pivot type reports

pearlmy — Mon, 19 Mar 2012 17:57:41 GMT

Thanks. Proc Tabulate seems cumbersome to me. Do you know if FREQ be used with more than a 2x2 table without page breaks for the additional dimensions? (Like Crosstabs in SPSS).

Re: Pivot type reports

PGStats — Mon, 19 Mar 2012 19:24:07 GMT

Proc freq is limited to frequencies. But the tables can be of any size . The procedure will put as many tables as will fit on a page except if you use the PAGE option. For one-way tables, if you want it tobegin the next table on the same page even if it will not fit completely then you can use the COMPRESS option.

Take another look at proc tabulate. Start simple with something like

Proc tabulate data=myDataSet;

class myLineVar1 myLineVar2 myColVar1 myColVar2;

var myStatVar;

tables myLineVar1*myLineVar2, myColVar1*myColVar2*myStatVar*N;

run;

Good luck.

Pivot type reports

pearlmy — Mon, 19 Mar 2012 20:27:37 GMT

I've used PROC TABULATE, but so infrequently that it is difficult for me to recall what I did without referencing the manual. With FREQ I wanted to know if there was a way of handling multiple dimensions in a single table. For example, A*B*C would, I believe produce a table of B*C for each level of A. Could it produce a single table with panel for each level of A (like what can be done with TABULATE)?

Pivot type reports

Ksharp — Tue, 20 Mar 2012 01:58:12 GMT

What is your data and what output do you like.

Maybe ODS + proc freq can get what you want.

Ksharp

Pivot type reports

pearlmy — Tue, 20 Mar 2012 17:54:09 GMT

I want to be able to produce a report A*B*C with variable B nested within variable A (with the cells being a frequency counter--like with FREQ). A and B appear as row variables annd C is a column variable. Example:

Pivot type reports

Tom — Tue, 20 Mar 2012 18:06:12 GMT

Did you try the LIST option?

tables a*b*c/list;

Pivot type reports

Hima — Tue, 20 Mar 2012 18:06:25 GMT

data have;
input (id Name Gender Date) (:$10.);
cards;
1           Dan              M                1/1/2012
1           Dan              M               12/23/2011
1           Dan              M               10/15/2011
1           Dan              M                9/17/2011
2           Carol            F                 8/12/2011
2           Carol            F                 7/15/2011
;
run;

data want;
set have;
by id;
name=ifc(first.id,name,' ');
gender=ifc(first.id,gender,' ');
run;

proc freq data = want;
tables id*name*gender / list missing norow nocol nopercent nocum out=temp;
run;

libname test 'c:\temp\test.xls';
proc sql;
drop table test.sheet1;
quit;
data test.sheet1;
set temp;
run;
libname test clear;

Pivot type reports

Hima — Tue, 20 Mar 2012 19:07:25 GMT

Here is another exmple that might help you

data want;
input grp $ deal $ first $;
cards;
FVJK TEST1 1
FVJK TEST2 1
FVJK TEST1 1
FVJK TEST3 1
FRGH TEST3 2
FRGH TEST3 2
FRGH TEST5 2
FRGH TEST6 2
run;

proc freq data = want;
tables grp*deal*first / list missing norow nocol nopercent nocum out=temp;
run;

data temp1;
set temp;
by grp;
grp=ifc(first.grp,grp,' ');
run;

proc print; run;

PROC EXPORT DATA= work.temp1
            OUTFILE= "C:Test.xls"
            DBMS=EXCEL REPLACE;
     SHEET="T";
RUN;

Obs grp deal first COUNT PERCENT

                        1     FRGH    TEST3      2        2        25.0
                        2                  TEST5      2        1        12.5
                        3                  TEST6      2        1        12.5
                        4     FVJK    TEST1      1        2        25.0
                        5                 TEST2      1        1        12.5
                        6                 TEST3      1        1        12.5

Pivot type reports

Ksharp — Wed, 21 Mar 2012 04:13:26 GMT

Oh. proc tabulate is better.

data want;
input grp $ deal $ first $;
cards;
FVJK TEST1 1
FVJK TEST2 1
FVJK TEST1 1
FVJK TEST3 1
FRGH TEST3 2
FRGH TEST3 2
FRGH TEST5 2
FRGH TEST6 2
run;
proc freq data = want noprint ;
tables grp*deal*first / list nopercent nocum out=temp(drop=percent);
run;
ods tagsets.excelxp file='c:\temp\x.xls' style=sasweb;
proc tabulate data=temp;
class grp deal first ;
var count;
table grp=''*(deal='' all='sub_total'),first=''*count*sum='';
run;
ods tagsets.excelxp close;

Ksharp

Pivot type reports

pearlmy — Thu, 22 Mar 2012 18:19:33 GMT

I guess it comes back to TABULATE and ODS being the best tools for this. Thanks for your help.