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
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
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 |
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...
i don't have a clue what that means - how's this:
1 Leeds Total Total 450
data have; input code name $ breakdown $ type $ measure_Value; datalines;
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;
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.
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;
This is excellent thanks Patrick! Just what I needed 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.