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.
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;
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;
Switch N and __character_ in your table statement?
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.