topic Re: How to count number of entries in each column and find empty columns in SAS Data Management

How to count number of entries in each column and find empty columns

ilikesas — Sat, 20 Dec 2014 01:26:57 GMT

Hi,

suppose I have a very big table with millions of rows. Some columns are almost full, some less, some have very few data and some are completely empty.

Is it possible to count the number of entries that are in each column?

Thank you

Re: How to count number of entries in each column and find empty columns

art297 — Sat, 20 Dec 2014 01:49:57 GMT

You could do something like the following:

data test;

set sashelp.class;

if _n_ eq 7 then do;

call missing(name);

call missing(height);

end;

run;

proc format;

value $missfmt ' '='Missing' other='Not Missing';

value missfmt . ='Missing' other='Not Missing';

run;

proc freq data=test;

format _CHARACTER_ $missfmt.;

tables _CHARACTER_ / missing nocum nopercent;

format _NUMERIC_ missfmt.;

tables _NUMERIC_ / missing nocum nopercent;

run;

Re: How to count number of entries in each column and find empty columns

Patrick — Sat, 20 Dec 2014 02:13:08 GMT

Below code counting the number of distinct values per column in a table. Is that what you're after? It could become quite a big table if you're having a lot of different values.

data sample;

set sashelp.class;

if _n_=5 then call missing(sex);

run;

ods _all_ close;

ods output OneWayFreqs = Freqs(keep=table F_: Frequency rename=(table=Varname));;

proc freq data=sample ;

table _all_ /nocum nopercent missing;

quit;

ods _all_ close;

ods listing;

data want(keep=Varname Value Frequency);

set freqs;

Varname=prxchange('s/^table(.*)/\1/oi',1,Varname);

Value=coalescec(of F_:);

run;

/*proc print data=want;*/

/*run;*/

Re: How to count number of entries in each column and find empty columns

ilikesas — Sat, 20 Dec 2014 02:42:31 GMT

Hi Arthur and Patrick, thanks for the quick reply and both of your codes are very helpful!!

Just some small extra questions if I may:

when doing the proc freq, I realized that some columns which I thought were completely empty and which I was going to delete actually had some data, albeit small, which I might use in the future.

1) Is it possible to see what is the number of the observation where these data are present ?

2) is it possible to make an output file of all the columns but conditional on data being present on columns of my choice?

Again thank you very much!!!

Re: How to count number of entries in each column and find empty columns

art297 — Sat, 20 Dec 2014 02:46:49 GMT

Not sure what you're asking. You could always use a datastep, with a where statement, only selecting records that have non-missing values for the variables you are interested in.

Re: How to count number of entries in each column and find empty columns

ilikesas — Sat, 20 Dec 2014 02:50:58 GMT

Hi arthur,

I have millions of lines of code, and in a special column of interest there are around 1000 rows with nonmissing data, and the rest are missing.

Is it possible to know where those nonmissing values are, i.e to output their observation number?

Thank you

Re: How to count number of entries in each column and find empty columns

ilikesas — Sat, 20 Dec 2014 03:09:45 GMT

Hi Arthur,

I actually figured out how to do my second little extra question, its quite simple if I'm correct and is of the following:

data a;

input name$ nu ;

datalines;

a 1

s 3

d .

g 4

t .

kk .

f 6

;

run;

data b;

set a;

if nu ne " ";

run;

thanks!

Re: How to count number of entries in each column and find empty columns

art297 — Sat, 20 Dec 2014 04:17:44 GMT

Just about. But that, alone, won;t show you which observations were missing. How about adding one extra variable to your code:

data b;

set a;

if nu ne " ";

observation=_n_;

run;

Re: How to count number of entries in each column and find empty columns

ilikesas — Sat, 20 Dec 2014 04:39:31 GMT

Thanks!!

Re: How to count number of entries in each column and find empty columns

Ksharp — Sat, 20 Dec 2014 12:55:16 GMT