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
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;
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;
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.