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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: