topic Re: Count Non Blanks Across Multiple Columns in SAS Enterprise Guide

Count Non Blanks Across Multiple Columns

Willo13 — Tue, 02 Jul 2013 07:43:05 GMT

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!

Re: Count Non Blanks Across Multiple Columns

pali — Tue, 02 Jul 2013 12:29:13 GMT

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

Re: Count Non Blanks Across Multiple Columns

MichelleHomes — Tue, 02 Jul 2013 12:51:11 GMT

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#p1tth4ltf640din1ey86ubo2lky2.htm

Cheers,

Michelle

Message was edited by: Michelle Homes

Re: Count Non Blanks Across Multiple Columns

esjackso — Tue, 02 Jul 2013 12:55:14 GMT

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!

Re: Count Non Blanks Across Multiple Columns

Willo13 — Tue, 02 Jul 2013 17:25:28 GMT

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.

Re: Count Non Blanks Across Multiple Columns

Willo13 — Tue, 02 Jul 2013 17:26:33 GMT

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

Re: Count Non Blanks Across Multiple Columns

Willo13 — Tue, 02 Jul 2013 17:27:13 GMT

It does - thanks!

Re: Count Non Blanks Across Multiple Columns

MichelleHomes — Tue, 02 Jul 2013 18:43:15 GMT

Glad to hear.

Re: Count Non Blanks Across Multiple Columns

fred_major — Wed, 27 Aug 2014 12:23:23 GMT

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

Re: Count Non Blanks Across Multiple Columns

RW9 — Wed, 27 Aug 2014 12:30:10 GMT

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;

Re: Count Non Blanks Across Multiple Columns

fred_major — Wed, 27 Aug 2014 13:22:58 GMT

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

Re: Count Non Blanks Across Multiple Columns

RW9 — Wed, 27 Aug 2014 13:44:54 GMT

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;