The SAS Output Delivery System and reporting techniques

Produce exact table layout (tabulate) to report layout

Reply
Occasional Contributor
Posts: 6

Produce exact table layout (tabulate) to report layout

PROC TABULATE produces the desired layout that the user wanted.  I just could not duplicate this layout in PROC REPORT.  I needed this layout in PROC REPORT so I can add compute blocks for conditional formatting/coloring/highlights/boxing, etc.

 

proc format library=work;
   value  dtfmt 1 = 'Job Openings'
                2 = 'Hires'
                3 = 'Total Separations'
                4 = 'Quits'
                5 = 'Layoffs and Discharges'
                6 = 'Other Separations';
run;
 
proc tabulate data = all2 missing order=data format=best12.;
   class industry dtord / preloadfmt;
   var levelp levelc otml scl pct_sig;
   table industry='', dtord=''*(levelp levelc otml scl pct_sig)*sum=''
         / box=[label = 'Industry and region'] printmiss nocellmerge;
   format dtord dtfmt.;
run;

This is the content of the dataset ALL2.

                                          Alphabetic List of Variables and Attributes

                                 #    Variable    Type    Len    Format    Label

                                 5    INDUSTRY    Char     50              Industry and region
                                 7    LEVEL       Num       4
                                 6    ORD         Num       8
                                 8    OTML        Num       8    5.        OTM diff
                                 9    SCL         Num       8    5.        Critical Value
                                 2    dt          Char      2              Datatype

                                                                                                  08:37 Monday, May 9, 2016   2

                                                    The CONTENTS Procedure

                                          Alphabetic List of Variables and Attributes

                                 #    Variable    Type    Len    Format    Label

                                 3    dtord       Num       8              Ordered Datatype
                                11    levelc      Num       8              Level (t)
                                10    levelp      Num       8              Level (t-1)
                                 1    neword      Num       8
                                 4    pct_sig     Num       8              PCT SIG


                                                       Sort Information

                                               Sortedby       dtord neword LEVEL
                                               Validated      YES
                                               Character Set  ANSI

This is the desired layout as shown in EXCEL from PROC TABULATE that I want in PROC REPORT.

layout.png

 

I'm sure this must be simple but I'm not seeing it.  Any assistance is appreciated.

Super User
Super User
Posts: 7,695

Re: Produce exact table layout (tabulate) to report layout

Looks like you need to transpose the dataset, then apply some labels.  Post some test data (in the form of a datastep) to get some code.

Occasional Contributor
Posts: 6

Re: Produce exact table layout (tabulate) to report layout

Well, the dataset is a combination from several datasets together that were calculated by previous processes.  The dataset ALL2 is the resulting dataset.  I can post the dataset (7 vars and 192 obs).

 

Basically, there are 32 industry/region in the first colum "Industry and region" and the variable is industry.

 

Across, there are 6 datatypes (dtord by format dtfmt.).

 

Under each of the across 6 datatypes are 5 reporting values (levelp, levelc, otml, scl, pct_sig).

Occasional Contributor
Posts: 6

Re: Produce exact table layout (tabulate) to report layout

data all2;
   set all1(keep = dtord neword level industry levelp levelc otml scl pct_sig);
run;

proc sort data = all2;
  by dtord neword level;
run;

dtord = orders the datatype in numeric 1-6.

neworder = orders the industries in numeric 1-32 (times 6 = 192 obs)

level = indention level of the industry (planned for proc report to ident industries)

levelp = level (t-1)

levelc = level (t)

otml = o-t-m level chg

scl = critical value

pct_sig = significant pct chg

Super User
Super User
Posts: 7,695

Re: Produce exact table layout (tabulate) to report layout

Yes, just need some of the ALL2 dataset to work with.  You can get the test data in a postable format by following this post:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

 

Occasional Contributor
Posts: 6

Re: Produce exact table layout (tabulate) to report layout

Interesting!  Here it is.

Attachment
Super User
Super User
Posts: 7,695

Re: Produce exact table layout (tabulate) to report layout

So from your data:

data want (drop=dtord neword level);
  merge all2 (where=(dtord=1) rename=(levelp=levelp1 levelc=levelc1 otml=otml1 scl=scl1 pct_sig=pct_sig1))
        all2 (where=(dtord=2) rename=(levelp=levelp2 levelc=levelc2 otml=otml2 scl=scl2 pct_sig=pct_sig2))
        all2 (where=(dtord=3) rename=(levelp=levelp3 levelc=levelc3 otml=otml3 scl=scl3 pct_sig=pct_sig3))
        all2 (where=(dtord=4) rename=(levelp=levelp4 levelc=levelc4 otml=otml4 scl=scl4 pct_sig=pct_sig4))
        all2 (where=(dtord=5) rename=(levelp=levelp5 levelc=levelc5 otml=otml5 scl=scl5 pct_sig=pct_sig5))
        all2 (where=(dtord=6) rename=(levelp=levelp6 levelc=levelc6 otml=otml6 scl=scl6 pct_sig=pct_sig6));
  by neword;
  if level ne 0 then do;
    level=level-1;
    industry=cat(repeat("  ",level),strip(industry));
  end;
run;

ods tagsets.excelxp file="s:\temp\rob\test.xml" style=statistical;
proc report data=want nowd split="~";
  columns industry ('Job Openings' levelp1 levelc1 otml1 scl1 pct_sig1)
                   ('Hires' levelp2 levelc2 otml2 scl2 pct_sig2)
                   ('Total Separations' levelp3 levelc3 otml3 scl3 pct_sig3)
                   ('Quits' levelp4 levelc4 otml4 scl4 pct_sig4)
                   ('Layoffs and Discharges' levelp5 levelc5 otml5 scl5 pct_sig5)
                   ('Other Separations' levelp6 levelc6 otml6 scl6 pct_sig6);
  define  industry / "Industry and~region";
run;
ods tagsets.excelxp close;

You can then fiddle around with your proc report as you like.

Occasional Contributor
Posts: 6

Re: Produce exact table layout (tabulate) to report layout

Thanks!

 

I didn't realize it was necessary to break into grouped variables (levelp1, levelp2, etc.) in order to generate the necessary across columns.  It makes sense now as I think about this -- not sure why I didn't think of this.  Much appreciated, sir.

Super User
Posts: 11,112

Re: Produce exact table layout (tabulate) to report layout

If Proc Tabulate produces what your client wants why are you concerned with Proc Report?

Occasional Contributor
Posts: 6

Re: Produce exact table layout (tabulate) to report layout

The client wants additional row highlighting and conditional color cells as well as boxing between datatypes.  Some will require the use of a COMPUTE block.  The layout is what the user wanted.

Super User
Posts: 11,112

Re: Produce exact table layout (tabulate) to report layout

Background and text colors can be set per cell with formats applied using style overrides.

Depending on what you mean by "boxing between datatypes" that might be done with settings in a CLASSLev statement with style settings based on formats.

Super User
Posts: 9,865

Re: Produce exact table layout (tabulate) to report layout

How about this one :

 

data WORK.ALL2;
  infile datalines dsd truncover;
  input dtord:32. neword:32. level:32. industry:$50. levelp:BEST12. levelc:BEST12. otml:5. scl:5. pct_sig:32.;
datalines4;
1,1,0,Total nonfarm,4673,6254,246,213,115
1,2,1,Total private,4245,5703,242,210,115
1,3,1,Government,428,551,4,34,12
1,4,2,Mining and logging,14,26,8,7,114
1,5,2,Construction,91,148,28,41,68
1,6,2,Manufacturing,216,381,91,31,294
1,7,3,Durable goods,125,228,59,23,257
1,8,3,Nondurable goods,91,154,33,17,194
1,9,2,"Trade, transportation, and utilities",768,986,4,81,5
1,10,3,Wholesale trade,115,204,49,33,148
1,11,3,Retail trade,467,633,31,60,52
1,12,3,"Transportation, warehousing, and utilities",185,151,-75,27,278
1,13,2,Information,61,173,82,25,328
1,14,2,Financial activities,320,458,42,51,82
1,15,3,Finance and insurance,281,376,15,42,36
1,16,3,Real estate and rental and leasing,40,82,27,20,135
1,17,2,Professional and business services,1012,1233,-52,104,50
1,18,2,Education and health services,1003,1270,-10,58,17
1,19,3,Educational services,80,106,3,13,23
1,20,3,Health care and social assistance,923,1163,-14,56,25
1,21,2,Leisure and hospitality,657,810,-26,71,37
1,22,3,"Arts, entertainment, and recreation",59,86,10,18,56
1,23,3,Accommodation and food services,599,723,-36,65,55
1,24,2,Other services,105,221,75,47,160
1,25,2,Federal Government,64,77,-4,10,40
1,26,2,State and local government,364,474,8,31,26
1,27,3,State and local education,145,198,11,13,85
1,28,3,"State and local, excluding education",220,276,-4,25,16
1,29,2,Northeast,814,1080,35,83,42
1,30,2,South,1863,2326,-47,131,36
1,31,2,Midwest,1001,1340,53,76,70
1,32,2,West,995,1507,204,104,196
2,1,0,Total nonfarm,4746,6199,117,230,51
2,2,1,Total private,4429,5789,113,227,50
2,3,1,Government,318,411,4,32,13
2,4,2,Mining and logging,19,48,21,7,300
2,5,2,Construction,309,344,-44,72,61
2,6,2,Manufacturing,252,316,-5,43,12
2,7,3,Durable goods,138,202,23,29,79
2,8,3,Nondurable goods,114,114,-28,25,112
2,9,2,"Trade, transportation, and utilities",982,1267,11,92,12
2,10,3,Wholesale trade,121,158,3,35,9
2,11,3,Retail trade,693,892,6,67,9
2,12,3,"Transportation, warehousing, and utilities",168,217,2,36,6
2,13,2,Information,63,102,19,17,112
2,14,2,Financial activities,186,245,6,38,16
2,15,3,Finance and insurance,122,161,5,28,18
2,16,3,Real estate and rental and leasing,65,84,1,22,5
2,17,2,Professional and business services,948,1314,90,106,85
2,18,2,Education and health services,597,719,-38,50,76
2,19,3,Educational services,92,97,-18,16,113
2,20,3,Health care and social assistance,505,621,-21,46,46
2,21,2,Leisure and hospitality,902,1205,46,86,53
2,22,3,"Arts, entertainment, and recreation",118,170,17,33,52
2,23,3,Accommodation and food services,784,1036,30,76,39
2,24,2,Other services,173,227,5,47,11
2,25,2,Federal Government,41,44,-7,7,100
2,26,2,State and local government,277,366,10,31,32
2,27,3,State and local education,142,198,14,18,78
2,28,3,"State and local, excluding education",135,169,-3,20,15
2,29,2,Northeast,770,961,-19,90,21
2,30,2,South,1797,2377,70,140,50
2,31,2,Midwest,1076,1459,73,101,72
2,32,2,West,1103,1402,-6,102,6
3,1,0,Total nonfarm,4453,5735,39,233,17
3,2,1,Total private,4144,5310,13,231,6
3,3,1,Government,310,427,27,30,90
3,4,2,Mining and logging,32,40,-1,7,14
3,5,2,Construction,263,369,29,64,45
3,6,2,Manufacturing,239,301,-4,44,9
3,7,3,Durable goods,138,173,-3,33,9
3,8,3,Nondurable goods,102,129,-1,25,4
3,9,2,"Trade, transportation, and utilities",939,1236,32,93,34
3,10,3,Wholesale trade,113,128,-14,34,41
3,11,3,Retail trade,674,874,11,70,16
3,12,3,"Transportation, warehousing, and utilities",151,233,35,32,109
3,13,2,Information,72,31,-53,18,294
3,14,2,Financial activities,172,229,8,44,18
3,15,3,Finance and insurance,123,158,0,29,0
3,16,3,Real estate and rental and leasing,49,71,8,26,31
3,17,2,Professional and business services,914,1150,-15,101,15
3,18,2,Education and health services,537,645,-36,52,69
3,19,3,Educational services,87,77,-30,17,176
3,20,3,Health care and social assistance,450,568,-6,48,13
3,21,2,Leisure and hospitality,838,1139,59,84,70
3,22,3,"Arts, entertainment, and recreation",116,160,10,36,28
3,23,3,Accommodation and food services,722,979,49,69,71
3,24,2,Other services,139,169,-7,55,13
3,25,2,Federal Government,38,49,1,6,17
3,26,2,State and local government,271,376,26,28,93
3,27,3,State and local education,144,189,4,17,24
3,28,3,"State and local, excluding education",128,189,22,18,122
3,29,2,Northeast,714,956,38,95,40
3,30,2,South,1766,2278,19,138,14
3,31,2,Midwest,990,1237,-24,99,24
3,32,2,West,985,1264,5,110,5
4,1,0,Total nonfarm,2565,3475,172,130,132
4,2,1,Total private,2424,3271,151,129,117
4,3,1,Government,142,205,20,14,143
4,4,2,Mining and logging,17,15,-6,4,150
4,5,2,Construction,116,183,30,32,94
4,6,2,Manufacturing,130,148,-15,20,75
4,7,3,Durable goods,66,82,-2,14,14
4,8,3,Nondurable goods,64,67,-13,13,100
4,9,2,"Trade, transportation, and utilities",564,811,78,56,139
4,10,3,Wholesale trade,63,77,-3,20,15
4,11,3,Retail trade,425,592,43,45,96
4,12,3,"Transportation, warehousing, and utilities",77,141,38,17,224
4,13,2,Information,39,18,-27,12,225
4,14,2,Financial activities,77,143,38,28,136
4,15,3,Finance and insurance,56,101,26,18,144
4,16,3,Real estate and rental and leasing,21,40,12,16,75
4,17,2,Professional and business services,471,636,30,56,54
4,18,2,Education and health services,365,450,-14,30,47
4,19,3,Educational services,50,53,-9,8,113
4,20,3,Health care and social assistance,315,396,-6,28,21
4,21,2,Leisure and hospitality,564,772,44,56,79
4,22,3,"Arts, entertainment, and recreation",47,69,8,12,67
4,23,3,Accommodation and food services,518,703,36,53,68
4,24,2,Other services,82,98,-6,33,18
4,25,2,Federal Government,13,16,0,3,0
4,26,2,State and local government,130,189,20,14,143
4,27,3,State and local education,65,92,8,8,100
4,28,3,"State and local, excluding education",65,97,12,9,133
4,29,2,Northeast,358,500,37,49,76
4,30,2,South,1024,1456,128,82,156
4,31,2,Midwest,611,777,-3,55,5
4,32,2,West,572,743,11,64,17
5,1,0,Total nonfarm,1545,1830,-126,193,65
5,2,1,Total private,1438,1689,-129,190,68
5,3,1,Government,107,140,3,22,14
5,4,2,Mining and logging,11,23,8,4,200
5,5,2,Construction,141,176,-4,63,6
5,6,2,Manufacturing,92,121,3,37,8
5,7,3,Durable goods,59,70,-4,28,14
5,8,3,Nondurable goods,33,51,7,19,37
5,9,2,"Trade, transportation, and utilities",274,317,-28,74,38
5,10,3,Wholesale trade,41,39,-11,28,39
5,11,3,Retail trade,172,206,-12,58,21
5,12,3,"Transportation, warehousing, and utilities",61,74,-4,26,15
5,13,2,Information,29,8,-25,14,179
5,14,2,Financial activities,53,64,-3,28,11
5,15,3,Finance and insurance,29,36,-1,18,6
5,16,3,Real estate and rental and leasing,24,29,-2,18,11
5,17,2,Professional and business services,393,438,-56,78,72
5,18,2,Education and health services,140,154,-22,39,56
5,19,3,Educational services,33,21,-19,13,146
5,20,3,Health care and social assistance,107,133,-3,35,9
5,21,2,Leisure and hospitality,253,323,0,76,0
5,22,3,"Arts, entertainment, and recreation",65,90,6,34,18
5,23,3,Accommodation and food services,188,232,-7,63,11
5,24,2,Other services,53,64,-3,43,7
5,25,2,Federal Government,10,12,-1,3,33
5,26,2,State and local government,97,129,4,21,19
5,27,3,State and local education,55,64,-5,12,42
5,28,3,"State and local, excluding education",42,64,9,14,64
5,29,2,Northeast,296,361,-15,80,19
5,30,2,South,587,676,-64,103,62
5,31,2,Midwest,310,370,-22,85,26
5,32,2,West,354,421,-27,88,31
6,1,0,Total nonfarm,342,431,-5,55,9
6,2,1,Total private,282,350,-9,53,17
6,3,1,Government,60,82,4,9,44
6,4,2,Mining and logging,5,2,-3,2,150
6,5,2,Construction,5,10,3,11,27
6,6,2,Manufacturing,18,32,8,9,89
6,7,3,Durable goods,14,21,3,7,43
6,8,3,Nondurable goods,5,12,5,4,125
6,9,2,"Trade, transportation, and utilities",101,107,-19,24,79
6,10,3,Wholesale trade,10,13,0,10,0
6,11,3,Retail trade,77,77,-19,18,106
6,12,3,"Transportation, warehousing, and utilities",14,18,1,8,13
6,13,2,Information,5,6,0,4,0
6,14,2,Financial activities,41,23,-26,15,173
6,15,3,Finance and insurance,39,21,-25,13,192
6,16,3,Real estate and rental and leasing,4,2,-2,6,33
6,17,2,Professional and business services,50,76,11,21,52
6,18,2,Education and health services,32,41,0,14,0
6,19,3,Educational services,5,2,-3,3,100
6,20,3,Health care and social assistance,28,38,2,13,15
6,21,2,Leisure and hospitality,21,45,16,11,145
6,22,3,"Arts, entertainment, and recreation",5,2,-3,2,150
6,23,3,Accommodation and food services,16,43,19,11,173
6,24,2,Other services,4,7,2,11,18
6,25,2,Federal Government,16,22,1,3,33
6,26,2,State and local government,44,60,3,8,38
6,27,3,State and local education,24,32,1,4,25
6,28,3,"State and local, excluding education",21,28,1,6,17
6,29,2,Northeast,59,94,16,21,76
6,30,2,South,155,146,-45,32,141
6,31,2,Midwest,68,90,2,21,10
6,32,2,West,59,101,23,26,88
;;;;
run;
proc format library=work;
   value  dtfmt
                1 = 'Job Openings'
                2 = 'Hires'
                3 = 'Total Separations'
                4 = 'Quits'
                5 = 'Layoffs and Discharges'
                6 = 'Other Separations';
run;
proc report data=all2 nowd  ;
columns industry dtord,(levelp levelc otml scl pct_sig);
define industry/group order=data ;
define dtord/across ' ' order=internal ;
format dtord dtfmt.;
run;
Ask a Question
Discussion stats
  • 11 replies
  • 810 views
  • 1 like
  • 4 in conversation