BookmarkSubscribeRSS Feed
Eugenio211
Quartz | Level 8

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
3 REPLIES 3
PaigeMiller
Diamond | Level 26

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;

 

PaigeMiller_0-1662579291749.png

 

--
Paige Miller

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1448 views
  • 6 likes
  • 4 in conversation