topic Re: Looking for distinct name by a variable in New SAS User

Looking for distinct name by a variable

Sandeep77 — Wed, 10 May 2023 16:13:16 GMT

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;

Re: Looking for distinct name by a variable

Reeza — Wed, 10 May 2023 16:19:07 GMT

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?

Re: Looking for distinct name by a variable

PaigeMiller — Wed, 10 May 2023 16:30:52 GMT

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

Re: Looking for distinct name by a variable

Ksharp — Thu, 11 May 2023 11:51:33 GMT

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;

Re: Looking for distinct name by a variable

Sandeep77 — Fri, 12 May 2023 08:42:19 GMT

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;

Re: Looking for distinct name by a variable

Sandeep77 — Fri, 12 May 2023 08:49:26 GMT

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

Re: Looking for distinct name by a variable

Ksharp — Fri, 12 May 2023 11:39:49 GMT

proc sql;
create table want as
select *,count(distinct catx(' ',dr_inits,dr_name)) as n_name
 from name
  group by icustomerid ;
quit;