counting certain characters in a variable

[serrld113]

I have a variable that looks kind of like this:

variable1
FLZZZZZ
DFZZZZZ
DGLZZZZ
FMNZZZZ
DZZZZZZ
FGZZZZZ
FGLZZZZ

I need to count the occurrences of F 

the occurrences of L

the occurrences of L and F

and the occurrences of L or F

so that it looks kind of like this:

Just_F  |  Just_L  | L_and_F | L_or_F

    3             1              2              4

I've been using proc sql and trying COUNT

count(variable1,'FL')

sum(case when variable1 = 'F' then 1 else 0 end) as Just_F,
sum(case when variable1 = 'L' then 1 else 0 end) as Just_L,
sum(case when variable1 = 'F' or variable1 = 'L' then 1 else 0 end) as L_or_F,
sum(case when variable1 = 'F' and variable1 = 'L' then 1 else 0 end) as L_and_F

what I get from proc sql sum cases is all 0 I think because it's not taking into account the other characters.

Please Help! 🙂

[Reeza]

You're checking if the variable is equal to the F/L rather than counting them in that code. 

Use COUNT or COUNTW. 

[Shmuel]

I'm not sure you can do it with proc sql.

Using datastep you can try:

data have;
   input var1 $;
cards;
FLZZZZZ
DFZZZZZ
DGLZZZZ
FMNZZZZ
DZZZZZZ
FGZZZZZ
FGLZZZZ
; run;

data _null_;
 set have end=eof;
   retain Just_f Just_L L_and_F L_or_F ;
   array count  Just_f Just_L L_and_F L_or_F ;
   count_F=0; count_L=0;

   do i=1 to length(var1);
        char = substr(var1, i, 1);
        if char = 'F' then do; count_F=1; Just_F +1; end; else
        if char = 'L' then do; count_L=1; Just_L +1; end;
  end;
  if count_F and count_l then L_and_F +1;
  if Count_F or count_L then L_or_F +1;

  if eof then put Just_f= Just_L= L_and_F= L_or_F= ;
run;

[Shmuel]

You may prefer use function COUNTC (thanks to @Reeza😞

data have;
   input var1 $;
cards;
FLZZZZZ
DFZZZZZ
DGLZZZZ
FMNZZZZ
DZZZZZZ
FGZZZZZ
FGLZZZZ
; run;

data _null_;
 set have end=eof;
   retain Just_f Just_L L_and_F L_or_F ;
   array count  Just_f Just_L L_and_F L_or_F ;

   count_F = countc(var1,'F');
   count_L = countc(var1,'L');
   Just_F + count_F;
   Just_L + count_L;
   L_or_F = L_or_F + countc(var1,'LF');
   if count_F and count_L then L_and_F +1;

  if eof then put Just_f= Just_L= L_and_F= L_or_F= ;
run;

[Reeza · Accepted solution]

You can do this in SQL, but a data step seems much more straightforward to me, since its a single pass of the data as well.

proc sql ;
	create table want as select sum(FCount>0) as FTotal, sum(LCount>0) as LTotal, 
		sum(fcount>0 and lcount>0) as FLTotal, sum(fcount>0 or lcount>0) as 
		FLoptional from (select a.*, countc(var1, 'F') as FCount, countc(var1, 'L') 
		as LCount from have as a);
quit;