I am trying to calculate missing percentage of each variable in a dataset. Any help?

Reply
Contributor
Posts: 63

I am trying to calculate missing percentage of each variable in a dataset. Any help?

I am trying to calculate missing percentage of each variable in a data set. Any help?

I am using the following code but its not giving proper result.

proc sql;

   create table miss_var as

   select  nmiss(score_forum) / count(*) as miss_score_forum_pct ,

   nmiss(score_peer)/ count(*) as miss_score_peer_pct

   from fact_d_student;

quit ;

Any help please.....

Contributor
Posts: 42

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

Just use sum(missing(variable))/count(*) in your select statement and it should work just fine....

Contributor
Posts: 63

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

Yes, But i want to calculate missing value percentage of multiple variables of a dataset(almost all variables).

In this case i need to mention  all the variables.         

Contributor
Posts: 42

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

OK then if the problem is to access all the variables then try to run this macro

%macro missing_data(library=,dataset=,xoutdataset=);

proc sql noprint;

select name into :allvars separated by " " from dictionary.columns

where libname="%upcase(&library.)" and memname="%upcase(&dataset.)";

quit;

proc sql noprint;

create table &xoutdataset. as

select

%let firstspace=%sysfunc(find(&allvars.,%str( )));

%do %while (&firstspace.>0);

  %let var=%substr(&allvars.,1,&firstspace.);

  sum(missing(&var.))/count(*) as &var.,

  %let allvars=%substr(&allvars.,&firstspace.);

  %let firstspace=%sysfunc(find(&allvars.,%str( )));

%end;

'Y' as done_successfully

from &library..&dataset.;

quit;

%mend missing_data;

Hope it helps

Jakub

Contributor
Posts: 63

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

This macro is not working. It only gives a column done_successfullly as Y.

Contributor
Posts: 42

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

calling for example

%missing_data(library=sashelp,dataset=adomsg,xoutdataset=outfile);

works fine for me. I am running SAS 9.2 and have no problem there....

Jakub

Contributor
Posts: 63

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

I get the below output.

        done_successfully

1               Y

2               Y

3               Y

4               Y

5               Y

I need missing value percentage for each variable.

Contributor
Posts: 42

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

And how does the file outfile look like? It is

MSGIDMNEMONICLINENOLEVELTEXTdone_successfully
00000Y

for me...

Jakub

Contributor
Posts: 63

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

Unfortunately the file outfile i can only see done successfully column with 'Y'

I also get warning to the log

WARNING: Apparent symbolic reference ALLVARS not resolved.

Contributor
Posts: 42

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

odd, try this modification

%macro missing_data(library=,dataset=,xoutdataset=);

proc sql noprint;

select name into :allvars separated by " " from sashelp.vcolumn

where libname="%upcase(&library.)" and memname="%upcase(&dataset.)";

quit;

proc sql noprint;

create table &xoutdataset. as

select

%let firstspace=%sysfunc(find(&allvars.,%str( )));

%do %while (&firstspace.>0);

  %let var=%substr(&allvars.,1,&firstspace.);

  sum(missing(&var.))/count(*) as &var.,

  %let allvars=%substr(&allvars.,&firstspace.);

  %let firstspace=%sysfunc(find(&allvars.,%str( )));

%end;

'Y' as done_successfully

from &library..&dataset.;

quit;

%mend missing_data;

%missing_data(library=sashelp,dataset=adomsg,xoutdataset=outfile);

Contributor
Posts: 63

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

No diff:-(

gave same result with warnings

Contributor
Posts: 42

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

can you submit the line

options mprint mlogic symbolgen notes;

before running the macro and send me the extended log after running it via email? dzejmz@centrum.cz

Thanks

Contributor
Posts: 63

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

146  %missing_data(library=indices,dataset=lcs_an_fact_d_student,xoutdataset=outfile);

MLOGIC(MISSING_DATA):  Beginning execution.

MLOGIC(MISSING_DATA):  Parameter LIBRARY has value indices

MLOGIC(MISSING_DATA):  Parameter DATASET has value lcs_an_fact_d_student

MLOGIC(MISSING_DATA):  Parameter XOUTDATASET has value outfile

MPRINT(MISSING_DATA):   proc sql noprint;

SYMBOLGEN:  Macro variable LIBRARY resolves to indices

SYMBOLGEN:  Macro variable DATASET resolves to lcs_an_fact_d_student

MPRINT(MISSING_DATA):   select name into :allvars separated by " " from sashelp.vcolumn where

libname="INDICES" and memname="LCS_AN_FACT_D_STUDENT";

NOTE: No rows were selected.

MPRINT(MISSING_DATA):   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.07 seconds

      cpu time            0.01 seconds

MPRINT(MISSING_DATA):   proc sql noprint;

SYMBOLGEN:  Macro variable XOUTDATASET resolves to outfile

MLOGIC(MISSING_DATA):  %LET (variable name is FIRSTSPACE)

WARNING: Apparent symbolic reference ALLVARS not resolved.

SYMBOLGEN:  Macro variable FIRSTSPACE resolves to 0

MLOGIC(MISSING_DATA):  %DO %WHILE(&firstspace.>0) loop beginning; condition is FALSE.  Loop

      will not be executed.

SYMBOLGEN:  Macro variable LIBRARY resolves to indices

SYMBOLGEN:  Macro variable DATASET resolves to lcs_an_fact_d_student

MPRINT(MISSING_DATA):   create table outfile as select 'Y' as done_successfully from

indices.lcs_an_fact_d_student;

NOTE: Table WORK.OUTFILE created, with 50 rows and 1 columns.

MPRINT(MISSING_DATA):   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.03 seconds

MLOGIC(MISSING_DATA):  Ending execution.

Contributor
Posts: 42

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

The problem is here:

MPRINT(MISSING_DATA):   select name into :allvars separated by " " from sashelp.vcolumn where

libname="INDICES" and memname="LCS_AN_FACT_D_STUDENT";

NOTE: No rows were selected.

It seems that sas does not find your dataset in sashelp.vcolumn view.

Make sure the dataset for which you calculate the missing values is named LCS_AN_FACT_D_STUDENT and exists in the library INDICES. You need to find all the names of variables in this table otherwise nothing is calculated.

Super Contributor
Posts: 644

Re: I am trying to calculate missing percentage of each variable in a dataset. Any help?

Libname and memname must be entered or converted to capitals.

Richard

Ask a Question
Discussion stats
  • 25 replies
  • 3044 views
  • 0 likes
  • 8 in conversation