## counting certain characters in a variable

Solved
Occasional Contributor
Posts: 14

# counting certain characters in a variable

[ Edited ]

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!

Accepted Solutions
Solution
‎08-17-2017 10:45 AM
Super User
Posts: 23,771

## Re: counting certain characters in a variable

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;``````

All Replies
Super User
Posts: 23,771

## Re: counting certain characters in a variable

Posted in reply to serrld113

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

Use COUNT or COUNTW.

Trusted Advisor
Posts: 1,837

## Re: counting certain characters in a variable

Posted in reply to serrld113

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;``````
Trusted Advisor
Posts: 1,837

## Re: counting certain characters in a variable

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;``````
Solution
‎08-17-2017 10:45 AM
Super User
Posts: 23,771

## Re: counting certain characters in a variable

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;``````
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 177 views
• 2 likes
• 3 in conversation