BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

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.

4 REPLIES 4
data_null__
Jade | Level 19

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

saspert
Pyrite | Level 9

Ah! Good question. I think I told only half the story. Smiley Happy 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
Pyrite | Level 9

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__
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 945 views
  • 0 likes
  • 2 in conversation