Desktop productivity for business analysts and programmers

Sorting data in SAS EG

Reply
Occasional Contributor
Posts: 7

Sorting data in SAS EG

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

PROC Star
Posts: 1,334

Re: Sorting data in SAS EG

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

Occasional Contributor
Posts: 7

Re: Sorting data in SAS EG

CodeNameBreakdownTypeMeasure Value
1LeedsTotalTotal450
1LeedsMaleTotal180
1LeedsFemaleTotal270
1Leeds18-64Total160
1Leeds65 and overTotal290
1LeedsTotalDenominator450
1LeedsMaleDenominator180
1LeedsFemaleDenominator270
1Leeds18-64Denominator160
1Leeds65 and overDenominator290
1LeedsTotalNumerator450
1LeedsMaleNumerator180
1LeedsFemaleNumerator270
1Leeds18-64Numerator160
1Leeds65 and overNumerator290
3Manchester TotalTotal500
3Manchester MaleTotal195
3Manchester FemaleTotal305
3Manchester 18-64Total170
3Manchester 65 and overTotal171
3Manchester TotalDenominator500
3Manchester MaleDenominator195
3Manchester FemaleDenominator305
3Manchester 18-64Denominator170
3Manchester 65 and overDenominator171
3Manchester TotalNumerator500
3Manchester MaleNumerator195
3Manchester FemaleNumerator305
3Manchester 18-64Numerator170
3Manchester 65 and overNumerator171
2YorkTotalTotal500
2YorkMaleTotal195
2YorkFemaleTotal305
2York18-64Total170
Super User
Posts: 10,570

Re: Sorting data in SAS EG

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...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

Re: Sorting data in SAS EG

Posted in reply to KurtBremser
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;
Super User
Posts: 10,570

Re: Sorting data in SAS EG

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,795

Re: Sorting data in SAS EG

[ Edited ]

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;

 

 

Occasional Contributor
Posts: 7

Re: Sorting data in SAS EG

This is excellent thanks Patrick! Just what I needed Smiley Happy

Ask a Question
Discussion stats
  • 7 replies
  • 330 views
  • 2 likes
  • 4 in conversation