BookmarkSubscribeRSS Feed
RajasekharReddy
Fluorite | Level 6

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

3 REPLIES 3
Loko
Barite | Level 11

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;

Ksharp
Super User
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

stat_sas
Ammonite | Level 13

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 860 views
  • 3 likes
  • 4 in conversation