Subgroup with overlaps

[NTR]

good day,

May someone please assist me.

I have a dataset where I need to create subgroups, the problem though is that these groups overlap and its one variable that is in use.

example if data is as follows:

Acct number  ACC002CRT     Industry

1                         5              clothing

1                         3              cellular

1                         2              insurance

2                         1              loans

2                         0              insurance

3                         3              mortgage and bonds

4                         9              vehicle

4                       13              loans

now the subgroups that need to be created based on the data are:

if ACC_CRT >=12 then A01;

if  6 <=ACC_CRT <12 then A02;

if ACC_CRT >=9 then C01;

if ACC_CRT =1 then F22;

if ACC_CRT < 6 then B05;

etc.

also I need to count the number of industries each account is in.

e.g. 2 or more industries then C10;

An assistance to this will be highly appreciated.

[PaigeMiller]

You can create Multilabel Formats, this works in some situations, but not others (and you didn't really describe what you do after you create overlapping groups)

http://documentation.sas.com/?docsetId=proc&docsetTarget=p1upn25lbfo6mkn1wncu4dyh9q91.htm&docsetVers...

[Patrick]

@NTR

Based on the data and the rules you've posted: Can you please show us how the desired result should look like?

[NTR]

@ Patrick from the sample data provided above, the desired outcome would be....
for account 1: B05 and 3 or more industries.
account number 2: F22 and 2 industries.


I have managed to categorise the multilabel format.

now I'm struggling with the counting of the industries, if you may please assist.

[NTR]

so this is my data where I need assistance with counting number of industries for each observation.
Acct_no Industry Date
24541463 Home ware 01OCT2017
24541463 Finance houses 01OCT2017
24541463 Clothing 01OCT2017
24541463 Clothing 01OCT2017
24541463 Telecoms 01OCT2017
24541463 Micro lenders 01OCT2017
24280800 Personal loans 01JUL2017
24280800 Clothing 01JUL2017
24280800 Personal loans 01JUL2017
24280800 Personal loans 01JUL2017
24280800 Clothing 01JUL2017
24280800 Clothing 01JUL2017
24280800 Clothing 01JUL2017
24280800 Clothing 01JUL2017
24541463 Clothing 01OCT2017
24541463 Finance houses 01OCT2017
24541463 General retail 01OCT2017
24541463 Clothing 01OCT2017
24541463 Home ware 01OCT2017
24541463 General retail 01OCT2017
24541463 Clothing 01OCT2017
24541463 General retail 01OCT2017
24541463 Clothing 01OCT2017
24541463 Finance houses 01OCT2017
24541463 Clothing 01OCT2017
24541463 Clothing 01OCT2017
24541463 Finance houses 01OCT2017
24541463 Home ware 01OCT2017
24541463 Micro lenders 01OCT2017

[ballardw]

---

@NTR wrote:
so this is my data where I need assistance with counting number of industries for each observation.
Acct_no Industry Date
24541463 Home ware 01OCT2017
24541463 Finance houses 01OCT2017
24541463 Clothing 01OCT2017
24541463 Clothing 01OCT2017

---

And where is the ACC_CRT or ACC002CRT variable you referenced in the first post?

Each "observation" above only has one industry. Do you mean per Acct_no? 

[PaigeMiller]

---

@NTR wrote:



now I'm struggling with the counting of the industries, if you may please assist.

---

Explain this. What exactly are you doing that isn't working? Show us the SASLOG. Show us (a portion of) the desired outcome as an actual table instead of words.

[NTR]

I want to created industry grouping i.e.

if industry in ('General retail', 'Clothing', 'Furniture', 'Home ware') then flag='retail';

if industry= 'Cellular' then flag="Cellular';
else flag ='other'

after this I want to determine how many industries each record falls into:

the desired outcome would be:

Acc_no industry_count retail_count
24541463 5 2
24280800 2 1
24541463 5 3


I hope this is more clear. I'm a new SAS user