Sorting data in SAS EG

[nnn123]

Hi,

Using the example attached, is it possible to filter the data so it goes (in order of priority):

1. A-Z ascending on 'Name' then

2. Breakdown column ordered 'Total', 'Male', 'Female', '18-64', '65 and over' then

3. Type column ordered 'Total', 'Denominator' then 'Numerator'

I have done this manually in Excel for now but would like it automating in my SAS job

Thanks,

James

[TomKari]

You'll need to put in your example as something other than a spreadsheet, as most of us won't open Office documents off the internet.

Tom

[nnn123]

Code	Name	Breakdown	Type	Measure Value
1	Leeds	Total	Total	450
1	Leeds	Male	Total	180
1	Leeds	Female	Total	270
1	Leeds	18-64	Total	160
1	Leeds	65 and over	Total	290
1	Leeds	Total	Denominator	450
1	Leeds	Male	Denominator	180
1	Leeds	Female	Denominator	270
1	Leeds	18-64	Denominator	160
1	Leeds	65 and over	Denominator	290
1	Leeds	Total	Numerator	450
1	Leeds	Male	Numerator	180
1	Leeds	Female	Numerator	270
1	Leeds	18-64	Numerator	160
1	Leeds	65 and over	Numerator	290
3	Manchester	Total	Total	500
3	Manchester	Male	Total	195
3	Manchester	Female	Total	305
3	Manchester	18-64	Total	170
3	Manchester	65 and over	Total	171
3	Manchester	Total	Denominator	500
3	Manchester	Male	Denominator	195
3	Manchester	Female	Denominator	305
3	Manchester	18-64	Denominator	170
3	Manchester	65 and over	Denominator	171
3	Manchester	Total	Numerator	500
3	Manchester	Male	Numerator	195
3	Manchester	Female	Numerator	305
3	Manchester	18-64	Numerator	170
3	Manchester	65 and over	Numerator	171
2	York	Total	Total	500
2	York	Male	Total	195
2	York	Female	Total	305
2	York	18-64	Total	170

[Kurt_Bremser]

Please post example data in a data step, like

data have;
input code name $ breakdown $ type $ measure_Value;
datalines;
1 Leeds Total Total 450
;
run;

This lets all people have an identical dataset with just copy/paste and submit. No tedious typing, no guessing, no errors.

If you need to convert a more complicated dataset, use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[nnn123]

i don't have a clue what that means - how's this:

data have;
input code name $ breakdown $ type $ measure_Value;
datalines;
1 Leeds Total Total 450
1 Leeds Male Total 180
1 Leeds Female Total 270
1 Leeds 18-64 Total 160
1 Leeds 65 and over Total 290
1 Leeds Total Denominator 450
1 Leeds Male Denominator 180
1 Leeds Female Denominator 270
1 Leeds 18-64 Denominator 160
1 Leeds 65 and over Denominator 290
1 Leeds Total Numerator 450
1 Leeds Male Numerator 180
1 Leeds Female Numerator 270
1 Leeds 18-64 Numerator 160
1 Leeds 65 and over Numerator 290
3 Manchester Total Total 500
3 Manchester Male Total 195
3 Manchester Female Total 305
3 Manchester 18-64 Total 170
3 Manchester 65 and over Total 171
3 Manchester Total Denominator 500
3 Manchester Male Denominator 195
3 Manchester Female Denominator 305
3 Manchester 18-64 Denominator 170
3 Manchester 65 and over Denominator 171
3 Manchester Total Numerator 500
3 Manchester Male Numerator 195
;
run;

[Kurt_Bremser]

Perfect. Now there's something to work with.

My first idea was also along the line of @Patrick's solution with formats for behind-the-scenes values that sort as intended.

[Patrick]

Not that pretty but something like below should work.

DATA have;
  INFILE DATALINES4
    DLM='|'
    truncover
    DSD;
  INPUT
    Code             : BEST32.
    Name             : $20.
    Breakdown        : $20.
    Type             : $20.
    Measure_Value    : BEST32.;
DATALINES4;
1|Leeds|Total|Total|450
1|Leeds|Male|Total|180
1|Leeds|Female|Total|270
1|Leeds|18-64|Total|160
1|Leeds|65 and over|Total|290
1|Leeds|Total|Denominator|450
1|Leeds|Male|Denominator|180
1|Leeds|Female|Denominator|270
1|Leeds|18-64|Denominator|160
1|Leeds|65 and over|Denominator|290
1|Leeds|Total|Numerator|450
1|Leeds|Male|Numerator|180
1|Leeds|Female|Numerator|270
1|Leeds|18-64|Numerator|160
1|Leeds|65 and over|Numerator|290
3|Manchester |Total|Total|500
3|Manchester |Male|Total|195
3|Manchester |Female|Total|305
3|Manchester |18-64|Total|170
3|Manchester |65 and over|Total|171
3|Manchester |Total|Denominator|500
3|Manchester |Male|Denominator|195
3|Manchester |Female|Denominator|305
3|Manchester |18-64|Denominator|170
3|Manchester |65 and over|Denominator|171
3|Manchester |Total|Numerator|500
3|Manchester |Male|Numerator|195
3|Manchester |Female|Numerator|305
3|Manchester |18-64|Numerator|170
3|Manchester |65 and over|Numerator|171
2|York|Total|Total|500
2|York|Male|Total|195
2|York|Female|Total|305
2|York|18-64|Total|170
2|York|65 and over|Total|330
2|York|Total|Denominator|500
2|York|Male|Denominator|195
2|York|Female|Denominator|305
2|York|18-64|Denominator|170
2|York|65 and over|Denominator|330
2|York|Total|Numerator|500
2|York|Male|Numerator|195
2|York|Female|Numerator|305
2|York|18-64|Numerator|170
2|York|65 and over|Numerator|330
;;;;
run;

proc format;
  invalue $breakdown_sort
    'Total'=1
    'Male'=2
    'Female'=3
    '18-64'=4
    '65 and over'=5
    other=99
  ;
  invalue $Type_sort 
    'Total'=1
    'Denominator'=2
    'Numerator'=3
    other=99
  ;
run;


proc sql;
  create table want as
  select *
  from have
  order by Name, input(breakdown,$breakdown_sort.), input(type,$type_sort.)
  ;
quit;

[nnn123]

This is excellent thanks Patrick! Just what I needed 🙂