Hi all,
I have a dataset with icustomerid, debt_code and names. I am trying to find how many unique icustomerid have different names. So I am counting distinct names by icustomerid. But I am not sure the right approach for this. Can you please suggest what can I do?
icustomerid | debt_code | rep_code | dr_inits | dr_name |
48196 | 367762168 | 131 | Tami | Baker |
48196 | 337656029 | 131 | Tami | Baker |
48196 | 302678693 | 131 | Tami | Baker |
Data name;
input icustomerid debt_code rep_code dr_inits dr_name;
datalines ;
48196 367762168 131 Tami Baker
48196 337656029 131 Tami Baker
48196 302678693 131 Tami Baker;
run;
No need to show us the "Excel"-like table. The SAS code is sufficient. However, as I have mentioned to you many times now, the code you have presented doesn't work. Please test your code before providing it to us to make sure it works (and please fix this code).
Apologies for that. I have changed the code for the dataset and now it works fine. I am looking for output where it shows count distinct names by icustomerid. So the icustomerid no. 200914 should be 1 as the icustomerid is same and name is also same. Also, the count for surname would be 1.
DATA Name;
INPUT customerid debt_code rep_code dr_inits $ dr_name $ dr_address1 $ dr_address2 $ dr_address3 $;
CARDS;
186071 423212679 131 Natalie Perrin 12 Pirbright Close Bilston West Midlands
190034 386693311 131 Vancere Irish 45 Mere Road Birmingham
200914 148062417 131 Nyaradzai Maranduie 24 Earl Rise London
200914 10150751 131 Nyaradzai Maranduie 24 Earl Rise London
204413 269064234 131 Claire Buchanan 77 Cambridge Road Bootle
;
RUN;
Data name;
infile cards expandtabs;
input icustomerid debt_code rep_code dr_inits $ dr_name $;
datalines ;
48196 367762168 131 Tami Baker
48196 337656029 131 Tami Baker
48196 302678693 131 Tami Baker
;
run;
proc sql;
create table want as
select icustomerid,count(distinct dr_name) as n_name
from name
group by icustomerid ;
quit;
proc sql;
create table want as
select *,count(distinct catx(' ',dr_inits,dr_name)) as n_name
from name
group by icustomerid ;
quit;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.
Find more tutorials on the SAS Users YouTube channel.