turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- counting certain characters in a variable

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-15-2017 10:38 AM - edited 08-15-2017 10:44 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

08-15-2017 11:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to serrld113

08-15-2017 10:52 AM

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

Use COUNT or COUNTW.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to serrld113

08-15-2017 10:55 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

08-15-2017 11:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

08-15-2017 11:11 AM

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