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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.