topic Re: Count number of 'NA' occurances in a dataset and print it in SAS Programming

Count number of 'NA' occurances in a dataset and print it

mdhtrivedi — Mon, 24 Sep 2018 02:29:40 GMT

Hello,

I have a dataset named Subset.csv". I want to count the number of 'NA' occuring in the dataset and want to print that. My dataset looks like below:-

Day5	Day6	Day7	City1	City2	City3
Friday	NA	NA	NA	Delhi/NCR	NA
NA	NA	NA	NA	NA	Mumbai
Friday	NA	NA	NA	NA	NA
Friday	Saturday	NA	Bangalore	NA	NA
NA	NA	NA	NA	Delhi/NCR	NA
Friday	Saturday	Sunday	Bangalore	NA	NA
NA	NA	NA	NA	Delhi/NCR	NA
NA	NA	NA	NA	Delhi/NCR	NA
NA	NA	NA	NA	NA	NA
Friday	Saturday	NA	NA	NA	NA

As an output I want in output window:-

count=44

I have tried below code snippet to achieve the result:-

data Subset;
		array SubsetArray{10,6} Day5-Day7 City1-City3; 
		do i=1 to 10;
     		do j=1 to 6;
     			if SubsetArray(i,j) = 'NA' then
       		    count+1;
     		end;
		end;
 proc print count;    
run;

But, I am getting error of "Too few variables defined for the dimension(s) specified for the array SubsetArray."

I am not sure whether I have defined two dimensional array in a wrong way.

Please help me to achieve my task.

Thank You

Re: Count number of 'NA' occurances in a dataset and print it

PGStats — Mon, 24 Sep 2018 03:31:03 GMT

Proper syntax:

data Subset;
set have end=done;
array a Day5-Day7 City1-City3; 
do i=1 to dim(a);
    count + (a{i} = "NA");
    end;
if done then output;
keep count;
run;

proc print data=subset;    
run;

untested

Re: Count number of 'NA' occurances in a dataset and print it

novinosrin — Mon, 24 Sep 2018 03:41:21 GMT

No need for arrays in my opinion

data have;
input (Day5	Day6	Day7	City1	City2	City3) (:$20.);
cards;
Friday	NA	NA	NA	Delhi/NCR	NA
NA	NA	NA	NA	NA	Mumbai
Friday	NA	NA	NA	NA	NA
Friday	Saturday	NA	Bangalore	NA	NA
NA	NA	NA	NA	Delhi/NCR	NA
Friday	Saturday	Sunday	Bangalore	NA	NA
NA	NA	NA	NA	Delhi/NCR	NA
NA	NA	NA	NA	Delhi/NCR	NA
NA	NA	NA	NA	NA	NA
Friday	Saturday	NA	NA	NA	NA
;

data w;
set have end=lr;
k=count(cats(of _all_),'NA');
want+k;
if lr;
put want=;
keep want;
run;

Re: Count number of 'NA' occurances in a dataset and print it

ChrisNZ — Tue, 25 Sep 2018 23:03:50 GMT

If the words might contain the letters NA, something like this works:

data _null_;
  set HAVE end=LASTOBS; 
  COUNT + count(catt('~',catx('~~',of _all_),'~'),'~NA~');
  if LASTOBS;
  putlog COUNT=;
run;

Re: Count number of 'NA' occurances in a dataset and print it

mdhtrivedi — Tue, 25 Sep 2018 04:28:08 GMT

with minor modifications, this worked. Thanks for the solution!!