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
- /
- SAS Procedures
- /
- How to categorize data columns of multiple entries...

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-20-2013 12:57 PM

data |
---|

3 |

1,2 |

2,3,4 |

3,5,6 |

1,3,7 |

8 |

Can anyone help with a solution for this problem? I have been trying to find a solution for the problem of categorizing multiple entries combinations in to groups.

Problem:

If entry is any number or combination with numbers 1,2,3 then group as A ;

If entry is any number or combination with numbers 4,5,6 then group as B ;

If entry is any combination with numbers containing A and B then group as Both;

If entry is any combination with numbers not containing A and B then group as other or unknown;

My approach is as follows but wish to include combinations of entries.

Data ;

if entry = '1,2,3' then group = "A" ;

if entry = '4,5,6' then group = "B" ;

if entry = '1,2,3' AND entry = '4,5,6' then group = "Both" ;

else if entry not in ('1,2,3','4,5,6') then group = "other or unknown" ;

run;

Accepted Solutions

Solution

10-20-2013
07:32 PM

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

Posted in reply to juls

10-20-2013 07:32 PM

Here is one thought, lots of hard coding and lack of robustness, but you may work out something better based on it.

**data** have;

input entry $20.;

cards;

3

1,2

2,3,4

3,5,6

1,3,7

8

;

**data** want;

array _t(**3**) $1.; /* '3' is subjective, you will need to know that or put a bigger number hoping to cover the ground.

You could also choose to run an additional pass to figure it out.*/

set have;

_a=**0**;_b=**0**; _ct=countw(entry); length group $**8**;

do _i=**1** to _ct;

_t(_i)=scan(entry,_i);

if _t(_i) in ('1','2','3') then _a=**1**;

else if _t(_i) in ('4','5','6') then _b=**1**;

end;

if _a+_b=**2** then group='both';

else if _a+_b=**0** then group='unknown';

else if _a+_b=**1** then do;

if _a=**1** then group='a';

else group='b';

end;

drop _:;

**run**;

Haikuo

All Replies

Solution

10-20-2013
07:32 PM

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

Posted in reply to juls

10-20-2013 07:32 PM

Here is one thought, lots of hard coding and lack of robustness, but you may work out something better based on it.

**data** have;

input entry $20.;

cards;

3

1,2

2,3,4

3,5,6

1,3,7

8

;

**data** want;

array _t(**3**) $1.; /* '3' is subjective, you will need to know that or put a bigger number hoping to cover the ground.

You could also choose to run an additional pass to figure it out.*/

set have;

_a=**0**;_b=**0**; _ct=countw(entry); length group $**8**;

do _i=**1** to _ct;

_t(_i)=scan(entry,_i);

if _t(_i) in ('1','2','3') then _a=**1**;

else if _t(_i) in ('4','5','6') then _b=**1**;

end;

if _a+_b=**2** then group='both';

else if _a+_b=**0** then group='unknown';

else if _a+_b=**1** then do;

if _a=**1** then group='a';

else group='b';

end;

drop _:;

**run**;

Haikuo

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

Posted in reply to juls

10-21-2013 09:01 AM

use a function like INDEX to populate six new binary variables that check for each value 1 - 6. then do some IF statements based on that.

For ex.

flag1-flag6=0;

if index(variable-name,'1') > 0 then flag1=1;

if index(variable-name,'1') > 0 then flag2=1;

etc...

then

if flag1 + flag2 + flag3 > 0 then do;

if flag4 + flag5 + flag6 = 0 then group = 'A';

else if...

else...