topic How to select one entry with multiple occurences in SAS Programming

How to select one entry with multiple occurences

NTR — Tue, 26 May 2020 11:08:35 GMT

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'

Re: How to select one entry with multiple occurences

ballardw — Tue, 26 May 2020 11:20:32 GMT

And how to tell "combined" or "unsecured"?

Do you need a data set, used for further analysis, or a report, people read these?

Re: How to select one entry with multiple occurences

Jagadishkatam — Tue, 26 May 2020 11:21:26 GMT

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;

Re: How to select one entry with multiple occurences

NTR — Tue, 26 May 2020 11:28:32 GMT

Secured = IndustryCode in (MB,VF)

Unsecured = IndustryCode not in (MB, VF)

Combined = either has (MB/VF) and any other code (PL,IN, CC)

Re: How to select one entry with multiple occurences

Ksharp — Tue, 26 May 2020 11:57:24 GMT

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;

Re: How to select one entry with multiple occurences

NTR — Tue, 26 May 2020 12:55:12 GMT

Thank you, this works perfectly.