Help using Base SAS procedures

counting certain characters in a variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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! Smiley Happy


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;

View solution in original post


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