topic Counting with Condition #3 in New SAS User

Counting with Condition #3

tonoplast — Tue, 02 Jul 2019 05:28:11 GMT

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,

Re: Counting with Condition #3

KachiM — Tue, 02 Jul 2019 06:06:16 GMT

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;

Re: Counting with Condition #3

PeterClemmensen — Tue, 02 Jul 2019 06:06:21 GMT

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;

Re: Counting with Condition #3

tonoplast — Tue, 02 Jul 2019 06:36:37 GMT

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

Re: Counting with Condition #3

tonoplast — Tue, 02 Jul 2019 06:38:06 GMT

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,