topic Re: Good Tabulate method for Character Missing Values in SAS Procedures

Good Tabulate method for Character Missing Values

Feragon42 — Tue, 11 Oct 2016 12:29:46 GMT

Hello community,

I'm new with this of SAS, and I have the titanic quest of improve the quality of a giant bussiness database. Why I introduce myself like this? Well, this is my first post, thanks to the community's paper I have been able to resolve the problem that cross in my way until now, and... I think it's not gonna be the last.

This time I need to do the next thing:

I have a dataset like this:

My work here is to make a table with the ammount of missing values.

With the number is easy, with a proc tabulate is enough, but... with the characters... well...

I tried with this Cody's code (From Cody's Data Cleaning Technique):

PROC FORMAT;
	VALUE $MISSCH ' ' = 'Missing' 
		    OTHER = 'NonMissing';
RUN;

PROC TABULATE DATA=WORK MISSING;
	CLASS _character_;
	TABLE _character_, N;
	FORMAT _character_ $MISSCH.;
RUN;

And I obtain a table like this:

But, I need this:

Someone can give me a tip?

Thanks for your attention, everybody.

Re: Good Tabulate method for Character Missing Values

Ksharp — Tue, 11 Oct 2016 13:08:12 GMT

data have;
input var1 $ var2;
cards;
some 1
thing 2
. 3
here .
. 4
;
run;
proc sql;
select 'var1' as var label='#' length=32,
 nmiss(var1) as missing,
 n(var1) as non_miss
 from have
union 
select 'var2' as var ,
 nmiss(var2) as missing,
 n(var2) as non_miss
 from have;
 
quit;

Re: Good Tabulate method for Character Missing Values

Reeza — Tue, 11 Oct 2016 13:23:48 GMT

Switch N and __character_ in your table statement?

Re: Good Tabulate method for Character Missing Values

RW9 — Tue, 11 Oct 2016 13:29:53 GMT

Can you not manipulate the output of a freq procedure to get your result:

data have;
 input var1 $ var2;
cards;
some 1
thing 2
. 3
here .
. 4
;
run;

proc freq data=have;
 ods output onewayfreqs=onewayfreqs;
 tables _all_;
run;

data onewayfreqs;
 set onewayfreqs (keep=table cumfrequency);
 by table;
 if last.table then output;
run;

proc sql;
 create table WANT as
 select A.TABLE,
 B.NOBS - A.CUMFREQUENCY as MISSING,
 A.CUMFREQUENCY as NONMISSING
 from ONEWAYFREQS A
 left join (select * from SASHELP.VTABLE where LIBNAME="WORK" and MEMNAME="HAVE") B
 on 1=1;
quit;