BookmarkSubscribeRSS Feed
Sandeep77
Lapis Lazuli | Level 10

 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;
6 REPLIES 6
Reeza
Super User
Please make a more representative example. Include a few different scenarios, specifically ones that may be problematic.
What do you expect as output from this data?
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

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;
Ksharp
Super User
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;
Sandeep77
Lapis Lazuli | Level 10
Thank you @Ksharp. It worked. Your code with table want gets the correct information but can you let me know what can I do to add all the variables from the previous table as the code only shows icustomerid and n_name variable. I want it to include all the variables from previous table. Also, is it possible to have distinct Dr_inits and Dr_name? I want both of them distinct so that the name and surname both matches exactly. Thanks
Ksharp
Super User
proc sql;
create table want as
select *,count(distinct catx(' ',dr_inits,dr_name)) as n_name
 from name
  group by icustomerid ;
quit;

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

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.

Discussion stats
  • 6 replies
  • 417 views
  • 2 likes
  • 4 in conversation