DATA Step, Macro, Functions and more

Count Characters Across Rows SAS

Reply
Contributor
Posts: 39

Count Characters Across Rows SAS

[ Edited ]

I have the following dataset:

 

CARNUM	INSPECTION_DATE	L1_FEELER_IN	L1_FEELER_OUT	L2_FEELER_IN	L2_FEELER_OUT	R1_FEELER_IN	R1_FEELER_OUT	R2_FEELER_IN	R2_FEELER_OUT	L3_FEELER_IN	L3_FEELER_OUT	L4_FEELER_IN	L4_FEELER_OUT	R3_FEELER_IN	R3_FEELER_OUT	R4_FEELER_IN	R4_FEELER_OUT
655552	07/23/2015	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
655519	08/28/2015	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO
655620	11/09/2015	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO
655600	12/31/2015	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES
655664	02/29/2016	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	NO
655768	04/27/2016	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES
560062	05/06/2015	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
655767	01/14/2016	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
655315	03/30/2016	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO
655659	07/16/2015	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
560027	04/12/2016	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO
655467	09/29/2015	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO	NO
655373	10/12/2015	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES
655644	01/12/2016	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES
655675	01/14/2016	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES	YES
655578	01/19/2016	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO
655669	01/20/2016	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO	YES	NO
655383	01/20/2016	YES	YES	YES	YES	NO	NO	YES	YES	NO	NO	YES	NO	YES	YES	YES	YES
655672	02/10/2016	YES	YES	YES	NO	YES	NO	YES	YES	YES	YES	YES	YES	YES	NO	YES	NO

 

Is there any sort of "countif" function in SAS that would allow me to count the number of "YES" "NO" or "N/A" by variable CARNUM?  Such that my result would look like this:

 

CARNUM INSPECTION_DATE YES NO N/A
655552 07/23/2015 0 0 16
655519 08/28/2015 0 16 0
655620 11/09/2015 0 16 0
655600 12/31/2015 16 0 0
655664 02/29/2016 15 1 0
655768 04/27/2016 16 0 0
560062 05/06/2015 0 0 16
655767 01/14/2016 0 0 16
655315 03/30/2016 0 16 0
655659 07/16/2015 0 0 16
560027 04/12/2016 8 8 0
655467 09/29/2015 0 16 0
655373 10/12/2015 16 0 0
655644 01/12/2016 16 0 0
655675 01/14/2016 16 0 0
655578 01/19/2016 8 8 0
655669 01/20/2016 8 8 0
655383 01/20/2016 11 5 0
655672 02/10/2016 12 4 0

 

I've been digging around and it seems like 'array' would be the best option, but I am not familiar with how it works to get this output.

 

Thanks,

Laners

Super User
Super User
Posts: 7,039

Re: Count Characters Across Rows SAS

Posted in reply to laneylaners

It would be much easier if you normalized your data structure.

 

proc transpose data=HAVE out=MIDDLE ;

   by carnum inspection_date NOTSORTED ;

   var L: R: ;

run;

proc freq data=middle ;

   by carnum inspection_date NOTSORTED ;

   table col1 / noprint out=want;

run;

proc print data=want ;

   var carnum inspection_date col1 count ;

run;

 

Super User
Posts: 11,343

Re: Count Characters Across Rows SAS

Posted in reply to laneylaners

An example with array:

data want;
   set have;
   array v $  L: R: ;
   do i= 1 to dim(v);
      Yes = sum(Yes,(v[i]='YES'));      
      No  = sum(No, (v[i]='NO'));      
      NA  = sum(NA, (v[i]='N/A'));      
   end;
   drop i  L: R: ;
run;
Contributor
Posts: 39

Re: Count Characters Across Rows SAS

This worked, thanks!
Trusted Advisor
Posts: 1,117

Re: Count Characters Across Rows SAS

Posted in reply to laneylaners

Or without arrays (a variant of ballardw's solution):

data want;
set have;
YES=countc(cat(of L: R:), 'Y');
NO =countc(cat(of L: R:), 'O');
NA =countc(cat(of L: R:), '/');
drop L: R:;
run;
Ask a Question
Discussion stats
  • 4 replies
  • 338 views
  • 3 likes
  • 4 in conversation