- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What do you expect as output from this data?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create table want as
select *,count(distinct catx(' ',dr_inits,dr_name)) as n_name
from name
group by icustomerid ;
quit;