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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.