topic Re: How to count duplicate names in proc sql in SAS Programming

How to count duplicate names in proc sql

BrahmanandaRao — Wed, 16 Oct 2019 11:32:20 GMT

proc sql;

create table emp_details (emp_idno num(10), emp_fname char(18),  emp_lname char(18), emp_dept num(6));					

insert into emp_details values(127323 'michale'         'robbin'               57);
insert into emp_details values(526689 'carlos'          'snares'               63);
insert into emp_details values(843795 'enric'           'dosio'                57);
insert into emp_details values(328717 'jhon'            'snares'               63);
insert into emp_details values(444527 'joseph'          'dosni'                47);
insert into emp_details values(659831 'zanifer'         'emily'                47);
insert into emp_details values(847674 'kuleswar'        'sitaraman'            57);
insert into emp_details values(748681 'henrey'          'gabriel'              47);
insert into emp_details values(555935 'alex'            'manuel'               57);
insert into emp_details values(539569 'george'          'mardy'                27);
insert into emp_details values(733843 'mario'           'saule'                63);
insert into emp_details values(631548 'alan'            'snappy'               27);
insert into emp_details values(839139 'maria'           'foster'               57);


quit;

i want duplicate emp_lname count

i tried in proc freq

proc freq data=emp_details;
quit;

any another method in proc and data step

Re: How to count duplicate names in proc sql

sbxkoenk — Wed, 16 Oct 2019 11:49:18 GMT

proc freq data=work.emp_details;
 tables emp_lname / out=work.count(where=(count>1));
run;

Re: How to count duplicate names in proc sql

Patrick — Wed, 16 Oct 2019 12:21:46 GMT

You can also use standard SQL for such a task

proc sql;
  create table emp_details (emp_idno num(10), emp_fname char(18),  emp_lname char(18), emp_dept num(6));
  insert into emp_details values(127323 'michale'         'robbin'               57);
  insert into emp_details values(526689 'carlos'          'snares'               63);
  insert into emp_details values(843795 'enric'           'dosio'                57);
  insert into emp_details values(328717 'jhon'            'snares'               63);
  insert into emp_details values(444527 'joseph'          'dosni'                47);
  insert into emp_details values(659831 'zanifer'         'emily'                47);
  insert into emp_details values(847674 'kuleswar'        'sitaraman'            57);
  insert into emp_details values(748681 'henrey'          'gabriel'              47);
  insert into emp_details values(555935 'alex'            'manuel'               57);
  insert into emp_details values(539569 'george'          'mardy'                27);
  insert into emp_details values(733843 'mario'           'saule'                63);
  insert into emp_details values(631548 'alan'            'snappy'               27);
  insert into emp_details values(839139 'maria'           'foster'               57);
quit;

proc sql;
/*  create table cnt_emp_details_cnt as*/
  select count(*) as emp_details_cnt, *
  from emp_details
  group by emp_lname
  ;
quit;