BookmarkSubscribeRSS Feed
pearlmy
Calcite | Level 5

What is the easiest way to create pivot type reports?

11 REPLIES 11
PGStats
Opal | Level 21

proc tabulate;

PG

PG
pearlmy
Calcite | Level 5

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

PGStats
Opal | Level 21

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
pearlmy
Calcite | Level 5

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

Ksharp
Super User

What is your data and what output do you like.

Maybe ODS  + proc freq can get what you want.

Ksharp

pearlmy
Calcite | Level 5

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

Tom
Super User Tom
Super User

Did you try the LIST option?

tables a*b*c/list;

Hima
Obsidian | Level 7

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;

Hima
Obsidian | Level 7

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

Ksharp
Super User

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

pearlmy
Calcite | Level 5

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

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
  • 11 replies
  • 1688 views
  • 0 likes
  • 5 in conversation