Hi
I have a very simple sorted data with observation as ID and Indicator, I would like to form a cluster for each ID with increments for each group of records. It should reset for every account type. I have thought a lot about it but no success so far. Please help . CLUSTER IS Required Output
ID Indicator Cluster
A11 . .
A11 . .
A11 1 1
A11 1 1
A11 1 1
A11 .
A11 .
A11 1 2
A11 1 2
A11 .
A11 1 3
A11 1 3
B11 .
B11 1 1
B11 1 1
B11 1 1
B11 .
B11 1 2
B11 .
B11 .
Use BY processing with NOTSORTED option. To zero out the CLUSTER number when indicator is missing you will need a second variable.
data have ;
input id $ indicator want;
cards;
A11 . .
A11 . .
A11 1 1
A11 1 1
A11 1 1
A11 . .
A11 . .
A11 1 2
A11 1 2
A11 . .
A11 1 3
A11 1 3
B11 . .
B11 1 1
B11 1 1
B11 1 1
B11 . .
B11 1 2
B11 . .
B11 . .
run;
data want ;
set have ;
by id indicator notsorted ;
if first.id then ncluster=0;
if first.indicator and not missing(indicator) then ncluster+1;
if not missing(indicator) then cluster=ncluster;
put id indicator want cluster;
drop ncluster ;
run;
Use BY processing with NOTSORTED option. To zero out the CLUSTER number when indicator is missing you will need a second variable.
data have ;
input id $ indicator want;
cards;
A11 . .
A11 . .
A11 1 1
A11 1 1
A11 1 1
A11 . .
A11 . .
A11 1 2
A11 1 2
A11 . .
A11 1 3
A11 1 3
B11 . .
B11 1 1
B11 1 1
B11 1 1
B11 . .
B11 1 2
B11 . .
B11 . .
run;
data want ;
set have ;
by id indicator notsorted ;
if first.id then ncluster=0;
if first.indicator and not missing(indicator) then ncluster+1;
if not missing(indicator) then cluster=ncluster;
put id indicator want cluster;
drop ncluster ;
run;
Many Thanks Tom. Don't know the usage of NotSorted option but will read over the internet.
Thanks again
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.