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.
I'm sure this must be simple but I'm not seeing it. Any assistance is appreciated.
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.
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).
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
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:
Interesting! Here it is.
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.
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.
If Proc Tabulate produces what your client wants why are you concerned with Proc Report?
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.