07-21-2011 11:31 AM
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.
African American 3
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.
07-21-2011 11:52 AM
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 -
2 18 - 34
3 35 - 44
4 45 - 54
5 Over 55
Does that help?
Appreciate your help very much.
07-22-2011 02:14 PM
So, I am trying to use the monotonic function to build the sort order based on the specification. My code is here -
MERGE DEMOGRAPHICS_BASE_&VALUE._OP(IN=A) &VALUE._ORDER_DEMO(IN=B);
IF A THEN DO;
Taking a specific context of macro variable Value to be Age
The values in GDR_ORDER_DEMO are here -
The values in DEMOGRAPHICS_BASE_GDR_OP are here-
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.
07-22-2011 03:56 PM
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_.