Counting with Condition #3

[tonoplast]

Dear Community,

I am stuck. Please help!

I am trying to label (count) without changing the sorting - by Condition.

I need the year of appearance as the first, and label new condition differently.

Have:

ID   Condition Year

ID1  ZZ  2005

ID1  XX  2006

ID1  XX  2007

ID1  ZZ  2008

ID1  ZZ  2009

ID1  YY  2010

ID2  BB  2006

ID2  BB  2007 

ID2  AA  2008

ID2  AA  2009

ID2  CC 2010

What I want:

ID   Condition Year  Want

ID1  ZZ  2005   1

ID1  XX  2006   2

ID1  XX  2007   2

ID1  ZZ  2008   1

ID1  ZZ  2009   1

ID1  YY  2010   3

ID2  BB  2006   1

ID2  BB  2007   1

ID2  AA  2008    2

ID2  AA  2009    2

ID2  CC 2010    3

Thank you!

Regards,

T

[KachiM · Accepted solution]

ID in the data set must be in the sorted order.

Collect the distinct Condition into a String sufficiently spaced (STR here) in one DoW loop.

Match the Condition in STR to determine its Order in it and write out the observation in the second DoW loop.

data Have;
input ID :$3. Condition :$2. Year;
datalines;
ID1  ZZ  2005
ID1  XX  2006
ID1  XX  2007
ID1  ZZ  2008
ID1  ZZ  2009
ID1  YY  2010
ID2  BB  2006
ID2  BB  2007 
ID2  AA  2008
ID2  AA  2009
ID2  CC 2010
;
run;

proc sort data = have;
by id;
run;

data want;
   length str $32767;
   do until(last.id);
      set have;
      by id;
      if find(str,Condition) = 0 then str = catx(' ', str, Condition);
   end;

   do until(last.id);
      set have;
      by id;
      want = findw(str, Condition, ' ', 'E');
      output;
   end;
run;

[tonoplast]

Thank you! This works great and you posted first, so I'll accept this as the asnwer.

[PeterClemmensen]

Classic use of the hash object 🙂

data have;
input ID $ Condition $ Year;
datalines;
ID1  ZZ  2005
ID1  XX  2006
ID1  XX  2007
ID1  ZZ  2008
ID1  ZZ  2009
ID1  YY  2010
ID2  BB  2006
ID2  BB  2007 
ID2  AA  2008
ID2  AA  2009
ID2  CC 2010
;

data want(drop=rc _:);
   declare hash h();
   h.defineKey("ID", "Condition");
   h.defineData("Want");
   h.defineDone();
   _Want=0;

   do until (last.ID);
      set have;
      by ID;
      if h.find() ne 0 then do;
         _Want+1;
         Want=_Want;
         rc=h.add();
      end;
      output;
   end;
run;

[tonoplast]

Thank you!

This works very well! I would have accepted this as the answer, but because of the order of the post, I'll have to accept the other one.

Thank you for introducing me to hash object. I'll have to look into it!

Regards,

T