Column data into row headers

Eugenio211 — Wed, 07 Sep 2022 18:53:39 GMT

Hello, hope all is well.

I have this proc summary data that I wanted to make one of the column data as header, can someone please show me how it's done. Thank you.

data:

business_state	Channel	product_type	Month_Name	Beg_Count
AB	Internet	M	Apr	932
AB	Internet	M	Aug	947
AB	Internet	M	Feb	966
AB	Internet	M	Jan	992
AB	Internet	M	Jul	932
AB	Internet	M	Jun	912
AB	Internet	M	Mar	967
AB	Internet	M	May	919
AB	Internet	M	Sep	1021
AB	Internet	O	Apr	827
AB	Internet	O	Aug	1041
AB	Internet	O	Feb	796
AB	Internet	O	Jan	782
AB	Internet	O	Jul	994
AB	Internet	O	Jun	933
AB	Internet	O	Mar	815
AB	Internet	O	May	871
AB	Internet	O	Sep	1137
AB	Store	M	Apr	652
AB	Store	M	Aug	830
AB	Store	M	Feb	675
AB	Store	M	Jan	680
AB	Store	M	Jul	714
AB	Store	M	Jun	663
AB	Store	M	Mar	673
AB	Store	M	May	654
AB	Store	M	Sep	1080
AB	Store	O	Apr	10171
AB	Store	O	Aug	11454
AB	Store	O	Feb	9878
AB	Store	O	Jan	9677
AB	Store	O	Jul	11187
AB	Store	O	Jun	10706
AB	Store	O	Mar	10063
AB	Store	O	May	10342
AB	Store	O	Sep	11742
BC	Internet	O	Apr	477
BC	Internet	O	Aug	621
BC	Internet	O	Feb	460
BC	Internet	O	Jan	447
BC	Internet	O	Jul	581
BC	Internet	O	Jun	533
BC	Internet	O	Mar	472
BC	Internet	O	May	508
BC	Internet	O	Sep	672
BC	Store	O	Apr	6870
BC	Store	O	Aug	8137
BC	Store	O	Feb	6534
BC	Store	O	Jan	6395
BC	Store	O	Jul	7812
BC	Store	O	Jun	7457
BC	Store	O	Mar	6724
BC	Store	O	May	7110
BC	Store	O	Sep	8549
MB	Store	M	Apr	210
MB	Store	M	Aug	188
MB	Store	M	Feb	253
MB	Store	M	Jan	254
MB	Store	M	Jul	183
MB	Store	M	Jun	191
MB	Store	M	Mar	234
MB	Store	M	May	195
MB	Store	M	Sep	206
MB	Store	O	Apr	1596
MB	Store	O	Aug	1979
MB	Store	O	Feb	1434
MB	Store	O	Jan	1372
MB	Store	O	Jul	1879
MB	Store	O	Jun	1774
MB	Store	O	Mar	1507
MB	Store	O	May	1668
MB	Store	O	Sep	2114
NB	Internet	M	Apr	17
NB	Internet	M	Aug	21
NB	Internet	M	Feb	19
NB	Internet	M	Jan	19
NB	Internet	M	Jul	19
NB	Internet	M	Jun	17
NB	Internet	M	Mar	17
NB	Internet	M	May	18
NB	Internet	M	Sep	22
NB	Internet	O	Apr	5
NB	Internet	O	Aug	14
NB	Internet	O	Feb	6
NB	Internet	O	Jan	5
NB	Internet	O	Jul	13
NB	Internet	O	Jun	10
NB	Internet	O	Mar	7
NB	Internet	O	May	9
NB	Internet	O	Sep	19
NB	Store	M	Apr	75
NB	Store	M	Aug	76
NB	Store	M	Feb	83
NB	Store	M	Jan	86
NB	Store	M	Jul	73
NB	Store	M	Jun	75
NB	Store	M	Mar	79
NB	Store	M	May	74
NB	Store	M	Sep	95
NB	Store	O	Apr	232
NB	Store	O	Aug	283
NB	Store	O	Feb	214
NB	Store	O	Jan	198
NB	Store	O	Jul	273
NB	Store	O	Jun	255
NB	Store	O	Mar	222
NB	Store	O	May	244
NB	Store	O	Sep	283
NL	Internet	O	Apr	6
NL	Internet	O	Aug	14
NL	Internet	O	Feb	5
NL	Internet	O	Jan	2
NL	Internet	O	Jul	12
NL	Internet	O	Jun	7
NL	Internet	O	Mar	5
NL	Internet	O	May	6
NL	Internet	O	Sep	19
NL	Store	O	Apr	61
NL	Store	O	Aug	152
NL	Store	O	Feb	38
NL	Store	O	Jan	32
NL	Store	O	Jul	125
NL	Store	O	Jun	95
NL	Store	O	Mar	46
NL	Store	O	May	73
NL	Store	O	Sep	177
NS	Internet	O	Apr	394
NS	Internet	O	Aug	494
NS	Internet	O	Feb	369
NS	Internet	O	Jan	356
NS	Internet	O	Jul	461
NS	Internet	O	Jun	435
NS	Internet	O	Mar	383
NS	Internet	O	May	419
NS	Internet	O	Sep	529
NS	Store	O	Apr	1142
NS	Store	O	Aug	1303
NS	Store	O	Feb	1116
NS	Store	O	Jan	1109
NS	Store	O	Jul	1258
NS	Store	O	Jun	1211
NS	Store	O	Mar	1139
NS	Store	O	May	1180
NS	Store	O	Sep	1357
ON	Internet	O	Apr	4519
ON	Internet	O	Aug	5672
ON	Internet	O	Feb	4276
ON	Internet	O	Jan	4211
ON	Internet	O	Jul	5294
ON	Internet	O	Jun	5015
ON	Internet	O	Mar	4408
ON	Internet	O	May	4734
ON	Internet	O	Sep	6162
ON	Store	O	Apr	36390
ON	Store	O	Aug	42103
ON	Store	O	Feb	35088
ON	Store	O	Jan	34813
ON	Store	O	Jul	40465
ON	Store	O	Jun	38656
ON	Store	O	Mar	35767
ON	Store	O	May	37202
ON	Store	O	Sep	44114
SK	Internet	O	Apr	262
SK	Internet	O	Aug	362
SK	Internet	O	Feb	228
SK	Internet	O	Jan	219
SK	Internet	O	Jul	328
SK	Internet	O	Jun	291
SK	Internet	O	Mar	245
SK	Internet	O	May	276
SK	Internet	O	Sep	396
SK	Store	O	Apr	1833
SK	Store	O	Aug	2399
SK	Store	O	Feb	1734
SK	Store	O	Jan	1689
SK	Store	O	Jul	2250
SK	Store	O	Jun	2059
SK	Store	O	Mar	1799
SK	Store	O	May	1917
SK	Store	O	Sep	2634
AB	Internet	O	Apr	1305
AB	Internet	O	Aug	2018
AB	Internet	O	Feb	1101
AB	Internet	O	Jan	988
AB	Internet	O	Jul	1836
AB	Internet	O	Jun	1620
AB	Internet	O	Mar	1196
AB	Internet	O	May	1424
AB	Internet	O	Sep	2137
BC	Internet	O	Apr	1053
BC	Internet	O	Aug	1607
BC	Internet	O	Feb	925
BC	Internet	O	Jan	861
BC	Internet	O	Jul	1447
BC	Internet	O	Jun	1290
BC	Internet	O	Mar	985
BC	Internet	O	May	1154
BC	Internet	O	Sep	1691
NB	Internet	O	Apr	30
NB	Internet	O	Aug	101
NB	Internet	O	Feb	19
NB	Internet	O	Jan	16
NB	Internet	O	Jul	79
NB	Internet	O	Jun	62
NB	Internet	O	Mar	23
NB	Internet	O	May	41
NB	Internet	O	Sep	125
NL	Internet	O	Apr	104
NL	Internet	O	Aug	222
NL	Internet	O	Feb	78
NL	Internet	O	Jan	60
NL	Internet	O	Jul	191
NL	Internet	O	Jun	159
NL	Internet	O	Mar	88
NL	Internet	O	May	123
NL	Internet	O	Sep	240
NS	Internet	O	Apr	182
NS	Internet	O	Aug	403
NS	Internet	O	Feb	117
NS	Internet	O	Jan	90
NS	Internet	O	Jul	336
NS	Internet	O	Jun	276
NS	Internet	O	Mar	144
NS	Internet	O	May	217
NS	Internet	O	Sep	449
NT	Internet	O	Apr	10
NT	Internet	O	Aug	25
NT	Internet	O	Feb	6
NT	Internet	O	Jan	3
NT	Internet	O	Jul	21
NT	Internet	O	Jun	16
NT	Internet	O	Mar	7
NT	Internet	O	May	11
NT	Internet	O	Sep	26
ON	Internet	O	Apr	3064
ON	Internet	O	Aug	4331
ON	Internet	O	Feb	2758
ON	Internet	O	Jan	2605
ON	Internet	O	Jul	3960
ON	Internet	O	Jun	3593
ON	Internet	O	Mar	2883
ON	Internet	O	May	3262
ON	Internet	O	Sep	4619
ON	Store	O	Apr	2461
ON	Store	O	Aug	3307
ON	Store	O	Feb	2232
ON	Store	O	Jan	2137
ON	Store	O	Jul	3055
ON	Store	O	Jun	2814
ON	Store	O	Mar	2343
ON	Store	O	May	2596
ON	Store	O	Sep	3572
PE	Internet	O	Apr	24
PE	Internet	O	Aug	70
PE	Internet	O	Feb	16
PE	Internet	O	Jan	11
PE	Internet	O	Jul	59
PE	Internet	O	Jun	43
PE	Internet	O	Mar	19
PE	Internet	O	May	27
PE	Internet	O	Sep	75
SK	Internet	O	Apr	263
SK	Internet	O	Aug	435
SK	Internet	O	Feb	240
SK	Internet	O	Jan	228
SK	Internet	O	Jul	369
SK	Internet	O	Jun	326
SK	Internet	O	Mar	245
SK	Internet	O	May	296
SK	Internet	O	Sep	474

Result want:

business_state	Channel	product_type	Jan	Feb	Mar
AB	Internet	M	932	1021	1137
AB	Internet	M	947	827	652
AB	Internet	M	966	1041	830
AB	Internet	M	992	796	675
AB	Internet	M	932	782	680
AB	Internet	M	912	994	714

Re: Column data into row headers

ghosh — Wed, 07 Sep 2022 18:59:01 GMT

Proc transpose long to wide. very easy give it a shot yourself

https://stats.oarc.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

Re: Column data into row headers

PaigeMiller — Wed, 07 Sep 2022 19:47:26 GMT

Yes, not hard to do, but putting calendar information into variable names and making a wide data set is generally considered as making your coding harder (not easier). If you need to create a report with months as (some of) the columns, then using PROC REPORT on a long (not wide) data set is a better method than transposing the data set.

In particular, with month names, these will sort alphabetically, and you will then have APR as the first month of the year, AUG as the second month of the year, and so on. Most people do not want this. (And you have made your coding harder, now you have to get the columns in the proper order). On the other hand, if you have actual numeric SAS date values to represent your months (or month/year combinations), these will sort numerically and PROC REPORT on the long data set will produce a table with month names in the desired order.

Also see:

Maxim 19

Long Beats Wide

Example with made up data in a long data set, and numeric SAS dates, everything sorts properly

data have;
    input product_type $ month :date9. count;
    cards;
M 01JAN2022 932
M 01FEB2022 947
M 01MAR2022 966
M 01APR2022 992
M 01MAY2022 932
M 01JUN2022 912
M 01JUL2022 967
M 01AUG2022 919
O 01FEB2022 823
O 01MAR2022 899
O 01APR2022 992
O 01MAY2022 932
O 01JUN2022 912
O 01JUL2022 967
O 01AUG2022 919
O 01SEP2022 848
;

proc report data=have;
    columns ("Product" product_type) month,count;
    define product_type/group ' ';
    define month/across order=internal format=monname3. ' ';
    define count/sum ' ';
run;

Re: Column data into row headers

Kurt_Bremser — Wed, 07 Sep 2022 19:27:56 GMT

Use PROC REPORT, and DEFINE month_name as a ACROSS variable.

topic Re: Column data into row headers in SAS Programming

Column data into row headers

Re: Column data into row headers

Re: Column data into row headers

Re: Column data into row headers