Proc tab - sorting subgroups in descending order when you've got a timeseries

Reply
New Contributor
Posts: 3

Proc tab - sorting subgroups in descending order when you've got a timeseries

I’m trying to use proc tabulate to produce a time series and I need to have the subgroup sorted or ordered in descending order by the most recent year. This is my current code:

proc tabulate data= grouped    format=8.0 format=comma8. missing  ;

class   group subgroup   fy_decided ;

var client_code;

table  (group='Stream' ) *(subgroup='Substream' all='Subtotal') all='Total',fy_decided = ' '

/rts=20 misstext=' ' box='' row=float;

keylabel  n=' ' ;

run;

The output looks like this:

Stream

Substream

2011/12

2012/13

2013/14

Stream 1

Substream a

1000

1000

1400

Substream b

18800

18200

20300

Substream c

200

400

600

Subtotal

20000

19600

22300

Stream 2

Substream f

1100

600

400

Substream g

10200

10100

10000

Substream h

4600

3800

6000

Subtotal

15900

14500

16400

I want it to look like this:

Stream

Substream

2011/12

2012/13

2013/14

Stream 1

Substream b

18800

18200

20300

Substream a

1000

1000

1400

Substream c

200

400

600

Subtotal

20000

19600

22300

Stream 2

Substream g

10200

10100

10000

Substream h

4600

3800

6000

Substream f

1100

600

400

Subtotal

15900

14500

16400

I would prefer doing it in the proc tab step but if its not possible is there a way in the data step?

Super User
Posts: 9,681

Re: Proc tab - sorting subgroups in descending order when you've got a timeseries

Add a white blank before  'Substream b' :

if Substream='Substream b'   then Substream='     Substream b' ;

Xia Keshan

Super User
Posts: 10,500

Re: Proc tab - sorting subgroups in descending order when you've got a timeseries

Depending on your actual data using

class  subgroup / order=freq;

class group fy_decided ;

Might work, at least it is easy to try.

Super User
Posts: 5,083

Re: Proc tab - sorting subgroups in descending order when you've got a timeseries

You can do this in the PROC step, if you prepare (and change the format slightly).

proc format;

   value $redo  'Substream a' = '2. Substream a'

'Substream b' = '1. Substream b'  'Substream c' = '3. Substream c'

'Substream g' = '1. Substream g'  'Substream h' = '2. Substream h'

'Substream f' = '3. Substream f';

run;

Then in the PROC TABULATE, specify ORDER = FORMATTED and add a FORMAT statement:

format substream $redo.;

You will need to know what substreams exist, and what order you would like them in, but it seems like you already know that.

Good luck.

New Contributor
Posts: 3

Re: Proc tab - sorting subgroups in descending order when you've got a timeseries

Hi

Ballardw, your suggestion doenst seem to work with a timeseries. It doesnt know to sort it for the latest year.

I dont want to format anything as the data changes so the order will change frequently. I dont want to have to reformat every time I run it specially as some of the tables i need to do have dozens of subgroups.

Is there no way to tell proc tab to sort by the latest year?

Super User
Posts: 5,083

Re: Proc tab - sorting subgroups in descending order when you've got a timeseries

That's a horse of a different color.  Perhaps the solution is for you to sort your data, and then use the ORDER=DATA option in the procedure.

Super User
Posts: 10,500

Re: Proc tab - sorting subgroups in descending order when you've got a timeseries

Your example data didn't appear to be a time series and the suggestion was based on the larger value of the default n count of the displayed values.

As I said, depending on you data, it might work. Provide some good example data and what the expected outcome is for that data.

New Contributor
Posts: 3

Re: Proc tab - sorting subgroups in descending order when you've got a timeseries

Maybe timeseries is the wrong word to use but basically as the data above shows ive got three years worth of data and sometimes the order of the substreams changes each year. In some of my tables the substream is country over the last 10 years. What i want is to have it sort the substream (say, country) by highest to lowest for just the latest year (2013/14) whilst still showing the previous years in the table. In the example below, it has listed the countries in their alphabetical order. In the example under that, you can see that what i want is to list them highest to lowest for the last year, 2013/14.

2009/102010/112011/122012/132013/14
Stream ACountry A195164220151199
Country B1,3181,1851,0011,7821,447
Country C491647672574544
Country D184168185175188
Country E6887541,1181,1221,383
Country F238175254182178
2009/102010/112011/122012/132013/14
Country B1,3181,1851,0011,7821,447
Country E6887541,1181,1221,383
Country C491647672574544
Stream ACountry A195164220151199
Country D184168185175188
Country F238175254182178
Ask a Question
Discussion stats
  • 7 replies
  • 327 views
  • 0 likes
  • 4 in conversation