topic Re: Subgroup with overlaps in SAS Programming

Subgroup with overlaps

NTR — Wed, 28 Mar 2018 07:53:45 GMT

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.

Re: Subgroup with overlaps

PaigeMiller — Wed, 28 Mar 2018 11:02:32 GMT

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&docsetVersion=9.4&locale=en

Re: Subgroup with overlaps

Patrick — Wed, 28 Mar 2018 11:33:53 GMT

@NTR

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

Re: Subgroup with overlaps

NTR — Wed, 28 Mar 2018 12:17:02 GMT

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

Re: Subgroup with overlaps

NTR — Wed, 28 Mar 2018 12:27:36 GMT

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

Re: Subgroup with overlaps

PaigeMiller — Wed, 28 Mar 2018 12:31:25 GMT

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

Re: Subgroup with overlaps

NTR — Wed, 28 Mar 2018 13:28:28 GMT

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

Re: Subgroup with overlaps

ballardw — Wed, 28 Mar 2018 14:48:50 GMT

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