How to count with condition in SAS

Question

I have a table with events from 1 to 11, The variables have 3 kinds of charcters like HSP,REP,OH. I need to count HSP by creating a new columns as OH1,OH2,OH3,OH4. The description of each column is

1) Number Of HSP before first OH
2) Number of HSP between 1st and 2nd OH
3) Number of HSP between 2nd and 3rd OH
4) Number of HSP between 3rd and 4th OH

SLN     EV1 EV2 EV3 EV4 EV5 EV6 EV7 EV8 EV9 EV10 EV11
120132  HSP OH  HSP HSP REP OH  HSP HSP HSP OH  REP
120135  OH  HSP HSP HSP HSP OH  HSP HSP HSP HSP OH
120200  OH  HSP OH  HSP HSP OH  HSP OH  HSP OH  HSP
120251  OH  HSP 
120318  HSP OH  HSP HSP HSP OH  HSP HSP HSP HSP HSP
120327  OH  HSP HSP HSP HSP OH  HSP HSP HSP HSP HSP
120509  HSP OH  HSP HSP HSP HSP OH  HSP HSP OH  HSP
120510  HSP HSP     

if there is no following 'OH' at all then set to missing E.g.120327: oh1=0, oh2=4, oh3=.

Answer 1

This should work:

data want;
  set have;
  x1=cat(of ev1-ev11);
  x2=' '||tranwrd(x1,'OH','|');
  array oh oh1-oh4;
  if index(x2,'|') then
  do i = 1 to 4;
    oh[i]=count(scan(x2,i,'|'),'HSP');
  end;
  else call missing(of oh1-oh4);
  drop x1-x2 i;
run;

x1 concatenates the codes into a long string. x2 adds pipes which makes the scan function in the oh calculations possible, it also adds a space at the front to allow oh1 to equal zero if the first code is 'OH'

You could use arrays or macros if you need to extend this beyond the 11 ev columns and 4 OH counts.

Edit: Added array for the OH count.

Edited to set nulls if no OH found.

REVISED

data counts;
input SLN EV1 $ EV2 $ EV3 $ EV4 $ EV5 $ EV6 $ EV7 $ EV8 $ EV9 $ EV10 $ EV11 $;
array EV[11] ;
array OH[4];
chk=0;
next = 1;
do i=1 to 4;
    oh[i] = 0;
end;
do i=1 to 11;
    if next > 4 then leave;
    if ev[i] = "HSP" then
        OH[next] = OH[next] + 1;
    else if ev[i] = "OH" then do;
        next = next + 1;
            chk+1;
        end;
    end;
    if chk<4 then do j=chk+1 to 4;
      oh[j]=.;
    end;



datalines;
120132  HSP OH  HSP HSP REP OH  HSP HSP HSP OH  REP
120135  OH  HSP HSP HSP HSP OH  HSP HSP HSP HSP OH
120200  OH  HSP OH  HSP HSP OH  HSP OH  HSP OH  HSP
120251  OH  HSP REP HSP REP HSP HSP HSP HSP OH  HSP
120318  HSP OH  HSP HSP HSP OH  HSP HSP HSP HSP HSP
120327  OH  HSP HSP HSP HSP OH  HSP HSP HSP HSP HSP
120509  HSP OH  HSP HSP HSP HSP OH  HSP HSP OH  HSP
120510  HSP OH  HSP REP HSP HSP OH  HSP HSP HSP HSP
;
run;