topic Re: Intelligent Sorting in SAS Programming

Intelligent Sorting

saspert — Thu, 21 Jul 2011 15:31:03 GMT

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.

Ethnicity Order

Asian 1

Caucasian 2

African American 3

Unkown 4

All 5

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.

Ethnicity Count Order

Caucasian 10 1

All 15 2

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

Thanks,

saspert.

Re: Intelligent Sorting

data_null__ — Thu, 21 Jul 2011 15:42:29 GMT

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

Intelligent Sorting

saspert — Thu, 21 Jul 2011 15:52:03 GMT

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

Re: Intelligent Sorting

saspert — Fri, 22 Jul 2011 18:14:59 GMT

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.

Re: Intelligent Sorting

data_null__ — Fri, 22 Jul 2011 19:56:24 GMT

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_.