Help using Base SAS procedures

Good Tabulate method for Character Missing Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Good Tabulate method for Character Missing Values

[ Edited ]

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:

Captura.PNG

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:

Captur1a.PNG

But, I need this:

Captura.PNG

Someone can give me a tip? 

Thanks for your attention, everybody.

 


Accepted Solutions
Solution
‎10-13-2016 09:40 AM
Super User
Posts: 9,682

Re: Good Tabulate method for Character Missing Values

[ Edited ]
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;

View solution in original post


All Replies
Solution
‎10-13-2016 09:40 AM
Super User
Posts: 9,682

Re: Good Tabulate method for Character Missing Values

[ Edited ]
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;
Super User
Posts: 17,842

Re: Good Tabulate method for Character Missing Values

Switch N and __character_ in your table statement? 

Super User
Super User
Posts: 7,407

Re: Good Tabulate method for Character Missing Values

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;

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 326 views
  • 2 likes
  • 4 in conversation