Hi all, I need help. I have a dataset of accounts that have multiple trades. I want to:
1. count number of trades each account has.
2. Determine if the account trades in a 'secured', 'unsecured' or 'combined' industry.
PS: secured trades are filtered as IndustryCode= MB or VF.
My dataset looks like this:
Acc_no IndustryCode
283 MB
286 PL
286 IN
286 CE
286 NL
286 FL
287 VF
287 CL
287 NL
I want output like the following:
Acc_no Num_Industries Indicator
283 1 'Secured'
286 5 'Unsecured'
287 3 'Combined'
And how to tell "combined" or "unsecured"?
Do you need a data set, used for further analysis, or a report, people read these?
Secured = IndustryCode in (MB,VF)
Unsecured = IndustryCode not in (MB, VF)
Combined = either has (MB/VF) and any other code (PL,IN, CC)
Please try the below code , however more information is required on indicator to program
data have;
input Acc_no IndustryCode$;
cards;
283 MB
286 PL
286 IN
286 CE
286 NL
286 FL
287 VF
287 CL
287 NL
;
proc sql;
create table want as select count(Acc_no) as count, Acc_no from have group by Acc_no;
quit;
data have;
input Acc_no IndustryCode$;
cards;
283 MB
286 PL
286 IN
286 CE
286 NL
286 FL
287 VF
287 CL
287 NL
;
proc sql;
create table want as
select acc_no,count(distinct industrycode) as num_industry,
case when(sum(industrycode in ('MB' 'VF'))=count(*)) then 'Secured'
when(sum(industrycode in ('MB' 'VF'))=0) then 'Unsecured'
else 'Combined' end as Indicator
from have
group by acc_no;
quit;
Thank you, this works perfectly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.