topic Re: unique observation count in SAS Data Science

unique observation count

RajasekharReddy — Fri, 10 Oct 2014 04:42:31 GMT

hi gm

can any one help me on how to write code for unique observation in one data set based on two variables without using proc sql

mock data set:

data count;

input id type$ city$;

101 saving nyj

101 account nyj

101 mutual nyj

102 saving Mex

102 account Mex

102 mutual mex

103 saving mex

103 mutual mex

104 saving nyj

104 mutual nyj

;

run;

/*I nedd result unique count for id based on varible city*/

result as expected:

1. count of unique id's (where city=nyj)= 02

2. count of unique id's (where city=mex)= 02

thanks

Re: unique observation count

Loko — Fri, 10 Oct 2014 07:48:39 GMT

Hello,

data have;
input id type$ city$;
datalines;
101 saving   nyj
101 account nyj
101 mutual   nyj
102 saving   mex
102 account mex
102 mutual   mex
103 saving   mex
103 mutual   mex
105 mutual   mex
104 saving   mex
104 saving   nyj
104 mutual   nyj
;
run;
proc sort data=have;
by city id;
run;
data want;
counter_unique_id=0;
do i=1 by 1 until (last.city);
set have (drop=type);
by city id;
if last.id then counter_unique_id=counter_unique_id+1;
if last.city then output;
end;

drop i id;
run;

Re: unique observation count

Ksharp — Fri, 10 Oct 2014 12:26:46 GMT

data have;
  input id type$ city$;
datalines;
101 saving   nyj
101 account  nyj
101 mutual   nyj
102 saving   mex
102 account  mex
102 mutual   mex
103 saving   mex
103 mutual   mex
105 mutual   mex
104 saving   mex
104 saving   nyj
104 mutual   nyj
  ;
run;
proc sort data=have;
by city id;
run;
ods listing close;
ods output nlevels=want;
proc freq data=have nlevels;
by city;
table id /norow nocol nopercent nocum;
run;
ods listing;

Xia Keshan

Re: unique observation count

stat_sas — Fri, 10 Oct 2014 14:43:03 GMT

proc sort data=have;
by city;
run;

data want(keep=city unique_count);
set have;
by city;
lag_id=id-lag(id);
if first.city then unique_count=1;
if not first.city and lag_id ne 0 then unique_count+1;
if last.city then output;
run;