topic Re: How to categorize data columns of multiple entries and combinations in to groups. in SAS Procedures

How to categorize data columns of multiple entries and combinations in to groups.

juls — Sun, 20 Oct 2013 16:57:24 GMT

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;

Re: How to categorize data columns of multiple entries and combinations in to groups.

Haikuo — Sun, 20 Oct 2013 23:32:58 GMT

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;

1,2

2,3,4

3,5,6

1,3,7

;

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

Re: How to categorize data columns of multiple entries and combinations in to groups.

cau83 — Mon, 21 Oct 2013 13:01:36 GMT

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