BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vk_2
Obsidian | Level 7

My data set is like this 

Customer_idPART_NPART_CTXN_ID
B1232688887902/7900159
B1231283982900/G89001278
B869128398203/890025/790217890
B29026888862820/12839179018

 

I am trying to achieve this 

PartCOUNT(Distinct Customer_id)COUNT(Distinct TXN_ID)
26888822
1283933
790222
790011
8290011
G890011
820311
89002511
6282011

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

1 REPLY 1
novinosrin
Tourmaline | Level 20
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
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
  • 1 reply
  • 639 views
  • 0 likes
  • 2 in conversation