BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Willo13
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com

View solution in original post

11 REPLIES 11
pali
Fluorite | Level 6

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
Quartz | Level 8

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
Calcite | Level 5

It does - thanks!

Willo13
Calcite | Level 5

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
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Willo13
Calcite | Level 5

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

MichelleHomes
Meteorite | Level 14

Glad to hear.

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 37584 views
  • 8 likes
  • 6 in conversation