Count Non Blanks Across Multiple Columns

[Willo13]

Hi,

I am a SAS beginner that has just started with EG 4.2, so feeling my way around. 

I have a table of data where the are multiple columns and I would like to count the number of non blank columns in each row and store the count into a new calculated column.  So my outcome should looks like this;

ID	Co1	Col2	Col3	Col4	New Column: Count of Non Blanks
1001	X			X	2
1002		X	1234	abc	3
1003	X				1

I've not been able to find any guidance on how to do this type of counting using EG query builder so if anyone could help that would be fab!

JW

[pali]

Hi JW,

See if it helps!

data new;

set old;

array chars(*)  _character_;

array num(*)  _numeric_;

count=0;

do i=1 to dim(chars);

if missing(chars(i))=0 then count=count+1;

end;

do j=1 to dim(num);
if missing(num(j))=0 then count=count+1;

end;

drop i j;

run;

-Saurabh

[esjackso]

Just to offer some more explanation (and a correction). Since you mentioned being new to SAS and started out using EG I will assume that you SAS coding is not a well defined skill at this point. The code that provided should work fine if you fit it into your program flow with a programming node. one correction needs to be made in that you want to count non blank variable entries but the missing function that is used looks for missing values and returns 1 if the variable is missing, so those two parts of the code should look for =0 and not =1.

The two array statements lists the variables you want the loop to look through. The way the code is written right now is one array looks through all numeric variables in the dataset and the other looks at all character variables in the dataset. The two do loops (do i ... end; do j ...end;), basically tell SAS to run through the array evaluating the missing function and if a the criteria is met increment the count variable by 1, Notice that count is set to 0 at the beginning of the data step so each observation begins with a default count of 0.

Hope this helps!

EJ

[Willo13]

It does - thanks!

[Willo13]

Many thanks for your reply.  It is really helpful but I am using EG so I may struggle to apply this code until I have learnt a little more.

[fred_major]

Hello,

If I wanted instead to count the number of times the total =  0  how would I modify this code?

data new;

set old;

array chars(*)  _character_;

array num(*)  _numeric_;

count=0;

do i=1 to dim(chars);

if missing(chars(i))=0 then count=count+1;

end;

do j=1 to dim(num);
if missing(num(j))=0 then count=count+1;

end;

drop i j;

run;

ID	C01	CO2	CO3	COL4	New Column:count of 0
1001	100	200	0	3	1
1002	4	17	0	0	2
1003	57	67	1	13	0

Thanks

Fred

[RW9]

Hi,

data want;

  set have;

  new_col=0;

  array items{4} col1-col4;

  do i=1 to 4;

    if items{i}=0 then new_col=new_col+1;

  end;

run;

[fred_major]

Hi Again,

Sorry perhaps I should have used my own data and not the original post.

Column names and number of columns do not stay consistent

_LABEL_	_25AUG2014	_24AUG2014	_23AUG2014	_22AUG2014	_21AUG2014
Fram	1	7	3	1310	1390
ans	47	16	87	231	805
Time	337	0	0	68	105

Fred

[RW9]

Ok.  Note, you would need to have all variables of the same type, e.g numeric. Otherwise its more complicated as you have to do the code twice, first to convert text to number.

You can pre-fill some macro variables and then use those, something like:

proc sql noprint;

  select  NAME

  into    :LIST separated by ' '

  from    SASHELP.VCOLUMN

  where   LIBNAME="WORK"

    and   MEMNAME="HAVE"

    and   index(NAME,"2014")>0;

  select  count(NAME)

  into    :LIST_COUNT

  from    SASHELP.VCOLUMN

  where   LIBNAME="WORK"

    and   MEMNAME="HAVE"

    and   index(NAME,"2014")>0;

quit;

Then, same as before, but using these macros:   

  data want;

  set have;

  new_col=0;

  array items{&LIST_COUNT.} &LIST.;

  do i=1 to &LIST_COUNT.;

    if items{i}=0 then new_col=new_col+1;

  end;

run;

[MichelleHomes · Accepted solution]

Hi JW,

in the Query Builder, you can create a Computed Column using an Advanced Expression and then specify the expression:

4-cmiss(of Col1 - Col4)

that is assuming your columns are named Col1, Col2, Col3 and Col4. If they are named something else such as A, B , C, D, E then you would use:

5-cmiss(A, B , C, D, E)

the cmiss function counts the number of missing numeric or character columns and so you want to subtract that from the number of variables you have.

For details on the cmiss function, see http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1tth4ltf64...

Cheers,

Michelle

Message was edited by: Michelle Homes

[Willo13]

Many thanks!  This works perfectly and has saved me so much time - brilliant!

[MichelleHomes]

Glad to hear.