Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

unique observation count

Reply
Contributor
Posts: 31

unique observation count

hi gm Smiley Happy

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

Super Contributor
Posts: 308

Re: unique observation count

Posted in reply to RajasekharReddy

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;

Super User
Posts: 10,044

Re: unique observation count

Posted in reply to RajasekharReddy
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

Trusted Advisor
Posts: 1,228

Re: unique observation count

Posted in reply to RajasekharReddy

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;

Ask a Question
Discussion stats
  • 3 replies
  • 311 views
  • 3 likes
  • 4 in conversation