Turn on suggestions

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

Showing results for

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

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-15-2017 10:38 AM
(5508 views)

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! 🙂

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.