BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Feragon42
Fluorite | Level 6

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

3 REPLIES 3
Ksharp
Super User
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;
Reeza
Super User

Switch N and __character_ in your table statement? 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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