topic Re: Counting unique records based on two variables in SAS Programming

Counting unique records based on two variables

kmardinian — Mon, 16 Dec 2019 19:29:11 GMT

Hi, I have a dataset where I have subject ID and site ID as two separate variables and I would like to count the unique subject IDs for each site id.

subject site

10021 644

10256 644

56985 733

45698 733

12659 733

And I would like the output dataset to look like this:

site count

644 2

733 3

Any help is much appreciated! Thank you!

Re: Counting unique records based on two variables

novinosrin — Mon, 16 Dec 2019 19:33:46 GMT

proc sql;
create table want as
select site, count(unique subject) as count
from have
group by site;
quit;

Re: Counting unique records based on two variables

ed_sas_member — Mon, 16 Dec 2019 19:34:10 GMT

Hi @kmardinian

You can do this:

data have;
	input subject site;
	cards;
10021 644
10021 644
10256 644
10256 644
56985 733
56985 733
45698 733
12659 733
;
run;
proc sql;
	create table want as
	select site, count(distinct subject) as count
	from have
	group by site;
run;

Re: Counting unique records based on two variables

Reeza — Mon, 16 Dec 2019 19:39:01 GMT

A double proc freq is the other option.

proc freq data=have noprint;
table site*subject / out=temp;
run;

proc freq data=temp;
table site / out=want;
run;

Re: Counting unique records based on two variables

novinosrin — Mon, 16 Dec 2019 19:41:24 GMT

if the data is grouped like your sample suggests, a datastep is convenient too

data have;
input subject $            site $;
cards;
10021             644
10021             644
10256             644
10256             644
56985             733
56985             733
45698             733
12659             733
;

data want;
set have;
by site subject notsorted;
if first.site then count=1;
else if first.subject then count+1;
if last.site;
drop subject;
run;

Re: Counting unique records based on two variables

novinosrin — Mon, 16 Dec 2019 19:53:32 GMT

Some HASH teaser 🙂

data have;
input subject $            site $;
cards;
10021             644
10256             644
10021             644
10256             644
56985             733
56985             733
45698             733
12659             733
;


data want;
if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("subject") ;
   h.definedata ("subject") ;
   h.definedone () ;
 end;
 do until(last.site);
  set have;
  by site;
  h.ref();
 end;
 count=h.num_items;
 h.clear();
 drop subject;
run;

Re: Counting unique records based on two variables

novinosrin — Mon, 16 Dec 2019 19:52:03 GMT

Key indexing paint brush . This assumes subjectid is numeric or char with only digit characters

data have;
input subject $            site $;
cards;
10021             644
10256             644
10021             644
10256             644
56985             733
56985             733
45698             733
12659             733
;

data want;
array t(-1000000:1000000)_temporary_;
do until(last.site);
  set have;
  by site;
  t(input(subject,32.))=1;
end;
count=n(of t(*));
call missing(of t(*));
run;

Re: Counting unique records based on two variables

kmardinian — Mon, 16 Dec 2019 20:39:35 GMT

Thank you!! This is exactly what I needed!

Re: Counting unique records based on two variables

Ksharp — Tue, 17 Dec 2019 12:03:07 GMT

Reeza ,

no need double, just one shot.

data have;
	input subject site;
	cards;
10021 644
10021 644
10256 644
10256 644
56985 733
56985 733
45698 733
12659 733
;

ods output nlevels=want;
proc freq data=have nlevels;
by site;
table subject;
run;