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 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.