Count Characters Across Rows SAS

laneylaners — Tue, 24 May 2016 21:20:35 GMT

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

Re: Count Characters Across Rows SAS

Tom — Tue, 24 May 2016 21:55:46 GMT

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;

Re: Count Characters Across Rows SAS

ballardw — Tue, 24 May 2016 22:07:57 GMT

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;

Re: Count Characters Across Rows SAS

FreelanceReinh — Wed, 25 May 2016 08:31:38 GMT

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;

Re: Count Characters Across Rows SAS

laneylaners — Wed, 25 May 2016 18:43:09 GMT

This worked, thanks!

topic Re: Count Characters Across Rows SAS in SAS Programming

Count Characters Across Rows SAS

Re: Count Characters Across Rows SAS

Re: Count Characters Across Rows SAS

Re: Count Characters Across Rows SAS

Re: Count Characters Across Rows SAS