Desktop productivity for business analysts and programmers

Count Non Blanks Across Multiple Columns

Reply
New Contributor
Posts: 4

Count Non Blanks Across Multiple Columns

Hi,

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

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;

IDCo1Col2Col3Col4New Column: Count of Non Blanks
1001XX2
1002X1234abc3
1003X1

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

Occasional Contributor
Posts: 5

Re: Count Non Blanks Across Multiple Columns

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

Super Contributor
Posts: 333

Re: Count Non Blanks Across Multiple Columns

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 ...endSmiley Wink, 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

New Contributor
Posts: 4

Re: Count Non Blanks Across Multiple Columns

It does - thanks!

New Contributor
Posts: 4

Re: Count Non Blanks Across Multiple Columns

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.

Contributor
Posts: 38

Re: Count Non Blanks Across Multiple Columns

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;

IDC01CO2CO3COL4New Column:count of 0
1001100200031
1002417002
100357671130

Thanks

Fred

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Count Non Blanks Across Multiple Columns

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;

Contributor
Posts: 38

Re: Count Non Blanks Across Multiple Columns

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
Fram17313101390
ans471687231805
Time3370068105

Fred

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Count Non Blanks Across Multiple Columns

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;

Super User
Posts: 1,230

Re: Count Non Blanks Across Multiple Columns

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

New Contributor
Posts: 4

Re: Count Non Blanks Across Multiple Columns

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

Super User
Posts: 1,230

Re: Count Non Blanks Across Multiple Columns

Glad to hear.

Ask a Question
Discussion stats
  • 11 replies
  • 14254 views
  • 5 likes
  • 6 in conversation