Subgroup with overlaps

Reply
Occasional Contributor NTR
Occasional Contributor
Posts: 7

Subgroup with overlaps

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.

 

Respected Advisor
Posts: 3,066

Re: Subgroup with overlaps

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

--
Paige Miller
Respected Advisor
Posts: 4,742

Re: Subgroup with overlaps

@NTR

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

Occasional Contributor NTR
Occasional Contributor
Posts: 7

Re: Subgroup with overlaps

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

Occasional Contributor NTR
Occasional Contributor
Posts: 7

Re: Subgroup with overlaps

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
Super User
Posts: 13,583

Re: Subgroup with overlaps


@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? 

Respected Advisor
Posts: 3,066

Re: Subgroup with overlaps


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

--
Paige Miller
Occasional Contributor NTR
Occasional Contributor
Posts: 7

Re: Subgroup with overlaps

Posted in reply to PaigeMiller
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
Ask a Question
Discussion stats
  • 7 replies
  • 110 views
  • 0 likes
  • 4 in conversation