Intelligent Sorting

[saspert]

Hi,

I am merging 2 datasets A and B. A is a lookup dataset with a specific sort order assigned to each category. B is a summary dataset with values assigned to some categories but not the entire list of categoies.

A                                  

Ethnicity                          Order

Asian                                   1

Caucasian                             2

African American                   3

Unkown                                 4

All                                        5

B

Ethnicity      Count

All                  15

Caucasian     10

I need the output dataset to be sorted intelligently. Because there are no other values, the sort order should be just 1 and 2.

C

Ethnicity  Count  Order

Caucasian  10      1

All             15       2

If this is familiar, can someone point out older discussion threads.

Thanks,

saspert.

[data_null__]

What difference does it make if ORDER has values (2,5) vs (1,2) the rows will still have the same order.

[saspert]

Ah! Good question. I think I told only half the story. So I need to merge it with 2 other datasets. So it could be something like this -

Ethnicity   Count Order  Age       Count Order  Gender    Count  Order

Caucasian  10     1        18-34      11         1     Male       29      1

All            15       2         45-54      36         2   All           35      2

                                        All      66        3     

The final proc report needs to show it like the table above but my original sort order are below - 

1 Unknown

2 18 - 34

3 35 - 44

4 45 - 54

5 Over 55

6 All

Does that help?

Appreciate your help very much.

saspert  

1 Unkown

2 Male

3 Female

4 All

[saspert]

Hi All,

So, I am trying to use the monotonic function to build the sort order based on the specification. My code is here -

DATA DEMOGRAPHICS_BASE_&VALUE._OP_SORTED;

MERGE DEMOGRAPHICS_BASE_&VALUE._OP(IN=A) &VALUE._ORDER_DEMO(IN=B);

BY DEMO_&VALUE._GROUPING;

IF A THEN DO;

/* &VALUE._SORT_ORDER=MONOTONIC();*/

OUTPUT;

END;

Taking a specific context of macro variable Value to be Age

The values in  GDR_ORDER_DEMO are here -

GDR_SORT_ORDER DEMO_GDR_GROUPING

4 All

3 Female

2 Male

1 Unkown

The values in DEMOGRAPHICS_BASE_GDR_OP are here-

DEMO_PRTCPTNG_USERS DEMO_GDR_GROUPING

169 All

74 Female

95 Male

When I merge them, I get the right dataset but the wrong sort order (using monotonic). Without monotonic, I get 2,3,4.

Hope it makes sense. Appreciate any help or any tips.

saspert.

[data_null__]

It sound like you have each summary in a separate data set,  Ethnicity Age and Gender. Is that right. 

If so you get the data sets in the proper order using your lookup tables and proc sort.  Once you get the rows ordered you no longer need ORDER. 

Then MERGE the data sets using a MERGE with no BY statement, (whatever that's being called nowadays).  Drop ORDER and rename COUNT in each data so they don't overwrite.  You can recreate ORDER if you like with a sum statement or from _N_.