CODEWITHSUNDEEP
sortingsas
AncientSwordRage
AncientSwordRage

Reputation: 7598

How to sort by defined order in sas, not alphabetically?

Say I have a dataset for a supermarket, with a product category, price product name etc. I want to sort by the category but with a defined order as opposed to alphabetically.

For instance if the categories are: canned, dairy, meat, vegetable, and I want to sort by when they may expire (I'll it's likely we'd have that information, so just play along please). This means I want to sort in this order: Dairy, Meat, Vegetable, Canned.

I wrote a macro with this signature:

key_sort(ds=, keys='canned, dairy, meat,  vegetable', field =category, sort_by=)

This parses the keys so that the can be put in a macro loop, then I use that much loop to write out a select statement like so:

Select(&field. ) ;
    %do i=1 %to &number_of_keys. ;
    %let current_key= %scan(&keys., &I., &delim.) ;
    When(&current_key. ) &field._key=&i. ;
    %end;
End;

I then sort by the &field._key
Is this the best method to take? Can this be done more succinctly or efficiently?

Upvotes: 1

Views: 4362

Answers (1)

Jon Clements
Jon Clements

Reputation: 142106

If you had a separate dataset that contained the ordering then you could utilise that and provide it as input a PROC FORMAT, or apply one that's only a few bits as simply as possible.

proc format; 
    value $EXPIRES 
        'canned'= 4
        'dairy' = 1
        'meat' = 2
        'vegetable' = 3
        other = 5
        ;
run; 

proc sql;
    CREATE TABLE output_set AS 
    SELECT * FROM foods ORDER BY put(produce_type, $expires.); 
quit;

Upvotes: 4

Related Questions