A string with a "/" delimiter has to be converted to indiviual value and find the distinct ids

Vk_2 — Fri, 24 Aug 2018 20:54:02 GMT

My data set is like this

Customer_id	PART_N	PART_C	TXN_ID
B123	268888	7902/7900	159
B123	12839	82900/G8900	1278
B869	12839	8203/890025/7902	17890
B290	268888	62820/12839	179018

I am trying to achieve this

Part	COUNT(Distinct Customer_id)	COUNT(Distinct TXN_ID)
268888	2	2
12839	3	3
7902	2	2
7900	1	1
82900	1	1
G8900	1	1
8203	1	1
890025	1	1
62820	1	1

So basically I am looking to split the Part_C to indiviual and combine with PART_N and find distinct (customer_id) and distinct(TXN_ID).

I have a list of distinct part numbers in a dataset (table2). If it was just PART_N then finding distinct (customer_id) and distinct(TXN_ID) would have been straight forward. SAS EG-7.12

Re: A string with a "/" delimiter has to be converted to indiviual value and find the dist

novinosrin — Fri, 24 Aug 2018 21:07:41 GMT

data have;
input (Customer_id	PART_N	PART_C	TXN_ID) (:$50.)	;
cards;
B123	268888	7902/7900	159
B123	12839	82900/G8900	1278
B869	12839	8203/890025/7902	17890
B290	268888	62820/12839	179018
;

data temp;
set have;
part=part_n;
output;
do _n_=1 to countw(part_c,'/');
part=scan(part_c,_n_,'/');
output;
end;
keep part Customer_id txn_id;
run;

proc sql;
create table want as
select part,COUNT(Distinct Customer_id) as Distinct_Customer_id,COUNT(Distinct TXN_ID)as Distinct_TXN_ID
from temp
group by part;
quit;

topic Re: A string with a "/" delimiter has to be converted to indiviual value and find the dist in SAS Programming

A string with a "/" delimiter has to be converted to indiviual value and find the distinct ids

Re: A string with a "/" delimiter has to be converted to indiviual value and find the dist