How to custom change group order of observations ?

Reply
Frequent Contributor
Posts: 137

How to custom change group order of observations ?

Hello SAS Folks,

First off, Thanks for taking the time to look into the my question on a weekend. I have a table with variable Dimension aligned and sorted BY groups. For example,

Dimension

automobile

automobile

automobile

automobile

aerospace

aerospace

aerospace

fighterjets

fighterjets

fighterjets

grandarmy

grandarmy

grandarmy

.....follows other groups etc

So yeah, the above group looks already sorted in the existing table. However my requirement is change the order of the groups according the business requirement. For example lets say if the above are in the order 1, 2, 3,,4, 5.. and I need to reorder like 3( starting from fighter jets),5,1,4,2. I would like help on a logic for that..In reality my table has 22 such groups..Any help please?

Thanks,

Charlotte

PROC Star
Posts: 1,093

Re: How to custom change group order of observations ?

Hi, Charlotte

You can set up a correlation dataset, that specifies the order for each dimension.

Here's some sample code:

data have;
length dimension $15;
input dimension;
cards;
automobile
automobile
automobile
automobile
aerospace
aerospace
aerospace
fighterjets
fighterjets
fighterjets
grandarmy
grandarmy
grandarmy
run;

data correlation;
length dimension $15;
input dimension correlation;
cards;
automobile 3
aerospace 4
fighterjets 1
grandarmy 2
run;

proc sql;
create table want as
  select h.dimension /* , h.variable2, h.variable3 ... */
from have h inner join correlation c on(h.dimension = c.dimension)
  order by c.correlation;
quit;

Tom

Frequent Contributor
Posts: 137

Re: How to custom change group order of observations ?

Hi Tom,

Hmm Nice one Sir. ..Well, I guess that pretty much settles my need, however if there was a case to re order the values within those Dimensions, would the approach be the same?

For example,

Dimension             Dimension_details

Fighterjets                French rafaele

Fighterjets                Sukoi

Fighterjets                F-16

--------------------------------------so on


Of course, It's not something I desperately need because the earlier question was only the business requirement and many thanks for that, however I am basically asking out of my own curiosity. The reason is if we were to custom reorder  Dimension_details and that were to be too many within each dimension, obviously the dataset would get large right? So bearing that in mind, wouldn't the process get tedious?Any thought Sir or perhaps I don't make sense at all?

Have a great day,

Charlotte from England



PROC Star
Posts: 1,093

Re: How to custom change group order of observations ?

Hi, Charlotte

I'm not quite sure what you're asking...

1. If you want to use the actual order for Dimension_details, it would just be

order by c.correlation h.Dimension_details;

2. If you wanted a custom dimension AND a custom Dimension_details, your correlation would need to be something like

automobile camry 3001

aerospace northrop 4001

fighterjets French rafaele 1001

fighterjets Sukoi 1002

fighterjets F-16 1003

grandarmy canada 2001

and then the SQL becomes

create table want as

  select h.dimension, h.Dimension_detail /* , h.variable3 ... */

from have h inner join correlation c on(h.dimension = c.dimension and h.Dimension_detail = c.Dimension_detail)

  order by c.correlation; /* untested */

So yes, you would need a list of both variables in the correct order, but wouldn't you have that anyway?

Fun stuff!

  Tom

Frequent Contributor
Posts: 137

Re: How to custom change group order of observations ?

Thank you Sir,

I understood better as I ran the query. Hmm Interesting you identified northrop. :smileygrin::smileycool:..I guess you know the kind I am working. Awesome!.

Hope You are having a great weekend. Many thanks once again.

Charlotte from England

Respected Advisor
Posts: 3,892

Re: How to custom change group order of observations ?

You could also use a format to define the sort order - and this can easily be repeated for sub-groups.

data have;
length dimension $15;
input dimension;
cards;
automobile
automobile
automobile
automobile
aerospace
aerospace
aerospace
fighterjets
fighterjets
fighterjets
grandarmy
grandarmy
grandarmy
run;

proc format;
  value $DimSort
    'fighterjets' = 1
    'grandarmy'   = 2 
    'automobile'  = 3
    'aerospace'   = 4
    ;
run;

proc sql;
  select *
  from have
  order put(dimension,$DimSort.)
  ;
quit;

Ask a Question
Discussion stats
  • 5 replies
  • 207 views
  • 8 likes
  • 3 in conversation