turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How to create a composite categorical variable fro...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 04:50 AM

Dear all,

I have 2 ordinal variables (quintiles).

Now I want to create a new categorical variable which combine all levels of those ordinal ones.

So my new var will have 25 categories.

Using if statement is really not a good solution.

Could anyone help me out with some tricks?

Thank you.

Accepted Solutions

Solution

05-28-2017
09:44 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-28-2017 05:20 PM

Minhtrang wrote:

Thank you very much, RW9, for your reply.

2 variables quintiles of Na and K are ordinal variables.

They were created from Proc rank with the continuous variables of Na, K.

This suggests to me you already have a data set created by 2 proc ranks, say variables Narank and Krank for each observation in the data set. If so, I suspect you want something like:

data want;

set have;

composite_rank=catx('_',narank,krank);

run;

This will create a character variable **composite_rank** as "1_1", "1_2", ... "5_4", "5_5". The CATX function works without objection enve when narank and krank are numerics.

If you want a numeric composite variable, then something like

composite_rank=10*narank+krank;

Just remember though: the numeric version assumes that krank is never more than 1 digit. If krank reachs 2 digits (say deciles 1 to 10), you have to multiple narank by 100.

A final note: the default of proc rank is to make qunitles 0 to 4 rather than 1 to 5. In that case, the number version of the composite will show only 1 digit when narank=0 - a good reason to make a character composite.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 04:54 AM

Please reveiw the guidance found underneath the Post button on post a new question:

Without anything to work with I can't really say anything.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 04:57 AM

Could you please present the sample data and the expected categorical output. This will help to get better response.

I believe using the format we could create the categorical variables, but a sample data will help me to confirm.

Thanks,

Jag

Jag

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 06:02 AM

Quintile of Na |
Quintile of K | NaKgroup |

1 | 1 | 11 |

2 | 2 | 12 |

3 | 3 | 13 |

4 | 4 | 14 |

5 | 5 | 15 |

21 | ||

22 | ||

23 | ||

24 | ||

25 |

Dear all,

My data looks as the table above.

I have 2 variables which are quintiles of Na and K (so each of them has 5 levels: 1st, 2nd,...5th quintile).

Now I want to create new var as NaKgroup which combines those 2 quintiles variables. This new variable will have 25 categories.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 06:07 AM

Again: **Please post test data in the form of a datastep.**

As such I now need to ask the question, are those variables nunmeric? Also, why do the first two columns only appear in the first five rows?

Something like:

data want; set have; nakgroup=input(cats(put(quit_na,best.),put(quint_k)),best.); run;

Assuming both columns are numeric and you want numeric result.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 06:38 AM

Thank you very much, RW9, for your reply.

2 variables quintiles of Na and K are ordinal variables. They were created from Proc rank with the continuous variables of Na, K.

Sorry I don't have SAS on the current PC so I can't post test data.

I just looked for hints.

I think I'll try your suggested solution with input statement.

Solution

05-28-2017
09:44 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-28-2017 05:20 PM

Minhtrang wrote:

Thank you very much, RW9, for your reply.

2 variables quintiles of Na and K are ordinal variables.

They were created from Proc rank with the continuous variables of Na, K.

This suggests to me you already have a data set created by 2 proc ranks, say variables Narank and Krank for each observation in the data set. If so, I suspect you want something like:

data want;

set have;

composite_rank=catx('_',narank,krank);

run;

This will create a character variable **composite_rank** as "1_1", "1_2", ... "5_4", "5_5". The CATX function works without objection enve when narank and krank are numerics.

If you want a numeric composite variable, then something like

composite_rank=10*narank+krank;

Just remember though: the numeric version assumes that krank is never more than 1 digit. If krank reachs 2 digits (say deciles 1 to 10), you have to multiple narank by 100.

A final note: the default of proc rank is to make qunitles 0 to 4 rather than 1 to 5. In that case, the number version of the composite will show only 1 digit when narank=0 - a good reason to make a character composite.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-28-2017 09:44 PM

Dear mkeintz,

You understood my problem so well.

Categorical variable is my desired outcome.

Thank you so much for your explanation in depth as well as the given optimal solution.

Best,

Trang

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 08:12 AM

Cartesian Product.

proc sql;

select cats(na,k) as new

from

(select distinct na from have),

(select distinct k from have)

;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-27-2017 04:59 PM

You can try below:

proc sql;

select cat(t1.NA,t2.K) as NAK

from temp t1,

temp t2;

run;