Help using Base SAS procedures

Pivot type reports

Reply
Occasional Contributor
Posts: 5

Pivot type reports

What is the easiest way to create pivot type reports?

Respected Advisor
Posts: 4,930

Pivot type reports

proc tabulate;

PG

PG
Occasional Contributor
Posts: 5

Pivot type reports

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

Respected Advisor
Posts: 4,930

Re: Pivot type reports

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.

PG

PG
Occasional Contributor
Posts: 5

Pivot type reports

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)?

Super User
Posts: 10,041

Pivot type reports

What is your data and what output do you like.

Maybe ODS  + proc freq can get what you want.

Ksharp

Occasional Contributor
Posts: 5

Pivot type reports

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:

table.PNG

Super User
Super User
Posts: 7,070

Pivot type reports

Did you try the LIST option?

tables a*b*c/list;

Regular Contributor
Posts: 233

Pivot type reports

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;

Regular Contributor
Posts: 233

Pivot type reports

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

Super User
Posts: 10,041

Pivot type reports

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

Occasional Contributor
Posts: 5

Pivot type reports

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

Ask a Question
Discussion stats
  • 11 replies
  • 487 views
  • 0 likes
  • 5 in conversation