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

⏰

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.** **

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.