BookmarkSubscribeRSS Feed
NTR
Fluorite | Level 6 NTR
Fluorite | Level 6

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.

 

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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
Patrick
Opal | Level 21

@NTR

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

NTR
Fluorite | Level 6 NTR
Fluorite | Level 6
@ 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
Fluorite | Level 6 NTR
Fluorite | Level 6
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
Super User

@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
Diamond | Level 26

@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
NTR
Fluorite | Level 6 NTR
Fluorite | Level 6
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 612 views
  • 0 likes
  • 4 in conversation