DATA Step, Macro, Functions and more

How to create a table to count non missing values for hundreds of columns?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

How to create a table to count non missing values for hundreds of columns?

Dear all,

 

I would like to create a table to count the number of non missing values for hundred of columns. My data looks like this:

 

col1 col2 col3 col4 ... col888

1       10     .       5  ...    23

56      .       4      23  ...  44

.         .       .       10  ...  23

1       10     .        5   ...    .

 

The table I would like to have is like this:

col1         3

col2         2

col3         1

col4         4

...

col888     3

 

Really appreciate any help and advice.

Thank you so much!!

 

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 19,878

Re: How to create a table to count non missing values for hundreds of columns?

Run a PROC MEANS with the NMISS output if they're all numeric.

 

If you need an example, see example 2 and 10 in the PROC MEANS documentation. 

If you still have issues post your code and log.

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Posts: 19,878

Re: How to create a table to count non missing values for hundreds of columns?

Run a PROC MEANS with the NMISS output if they're all numeric.

 

If you need an example, see example 2 and 10 in the PROC MEANS documentation. 

If you still have issues post your code and log.

Occasional Contributor
Posts: 18

Re: How to create a table to count non missing values for hundreds of columns?

Thank you!
Super User
Posts: 5,518

Re: How to create a table to count non missing values for hundreds of columns?

It is as simple as @Reeza described.  However, you will need to get N (not NMISS) to count nonmissing values.  A simple report:

 

proc means data=have n;

var _numeric_;

run;

 

Getting an output data set instead of a report is just slightly different:

 

proc means data=have noprint;

var _numeric_;

output out=want (drop=_type_ _freq_) n=;

run;

 

However, the shape of that data set might need to be adjusted (depends on how you intend to use it). 

Occasional Contributor
Posts: 18

Re: How to create a table to count non missing values for hundreds of columns?

Posted in reply to Astounding
Thank you a lot!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 86 views
  • 1 like
  • 3 in conversation