Solved: Counting number of missing data

wlierman · Posted 05-07-2021 03:36 PM

I am still working / struggling with the code that produces a count of missing and non-missing observations.

I have been following this code from an earlier solution. This is not the entire code --just to the point where I hit a snag as shown in the log below.

Data OPERA.OPERA_DIS_AGE;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage DPHYage AgAcq1st;

		do obs= 1 to 181308;

		         do i=1 to dim(vars);
				         vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
						 if vars(i) = '003' then call missing(vars(i));
						 
				 end;
				 output;
		end;
		drop i;
run;





/* First1, get variable names */
proc transpose data = OPERA.OPERA_DIS_AGE(obs = 0) out = OPERA.vname;
    var _all_;
run;


/* then check if variable is all missing */
proc sql noprint;
   Select catx('','n(',_name_,') as', _name_) into :vnames separated by ','
   From OPERA.vname;
   Create table temp As
     Select &vnames from OPERA.OPERA_DIS_AGE;
quit;

Data OPERA.OPERA_DIS_AGE;
618          call streaminit(45);
619          length obs 8.;
620          array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage
620! DPHYage AgAcq1st;
621
622          do obs= 1 to 181308;
623
624                   do i=1 to dim(vars);
625                           vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
626                           if vars(i) = '003' then call missing(vars(i));
627
628                   end;
629                   output;
630          end;
631          drop i;
632  run;

NOTE: The data set OPERA.OPERA_DIS_AGE has 181308 observations and 11 variables.
NOTE: Compressing data set OPERA.OPERA_DIS_AGE increased size by 12.50 percent.
      Compressed is 126 pages; un-compressed would require 112 pages.
NOTE: DATA statement used (Total process time):
      real time           0.35 seconds
      cpu time            0.32 seconds


633  proc transpose data = OPERA.OPERA_DIS_AGE(obs = 0) out = OPERA.vname;
634      var _all_;
635  run;

NOTE: Numeric variables in the input data set will be converted to character in the output
      data set.
NOTE: Compression was disabled for data set OPERA.VNAME because compression overhead would
      increase the size of the data set.
NOTE: There were 0 observations read from the data set OPERA.OPERA_DIS_AGE.
NOTE: The data set OPERA.VNAME has 11 observations and 1 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


636  /* then check if variable is all missing */
637  proc sql noprint;
638     Select catx('','n(',_name_,') as', _name_) into :vnames separated by ','
639     From OPERA.vname;
640     Create table temp As
641       Select &vnames from OPERA.OPERA_DIS_AGE;
                         ----
                         22
                         76
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT,
              FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER,
              OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: Line generated by the macro variable "VNAMES".
1     n(obs) asobs,n(DEYEage) asDEYEage,n(DMHDage) asDMHDage,n(DCOMage) asDCOMage,n(DEARage)
             ------
             1    22
WARNING 1-322: Assuming the symbol AS was misspelled as asobs.

ERROR 22-322: Expecting a name.

1  !  n(obs) asobs,n(DEYEage) asDEYEage,n(DMHDage) asDMHDage,n(DCOMage) asDCOMage,n(DEARage)
                              ---------
                              22
1  ! asDEARage,n(DLEAage) asDLEAage,n(DDRSage) asDDRSage,n(DOUTage) asDOUTage,n(DREMage)
1  ! asDREMage,n(DPHYage) asDPHYage,n(AgAcq1st) asAgAcq1st
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <,
              <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE,
              LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

642  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Thank you for continuing help and assistance.

wlierman

Reeza · Posted 05-07-2021 03:57 PM

Would you want output something like this?

If so, see the code here

Replace the CLASS data at the top with your input data set, Opera_DIS_AGE.

I'm not sure I understand how your code is supposed to work.

You do a transpose which gets you the list of variables (FYI - not really needed as you can use _numeric_ or _character_ in various places.)

But your SQL query is just a select of those variables? Were you intending to loop through each and do something like this?

select count(age) as n_age, count(*) as n_total, count(sex) as n_sex .....
from ...

Or did you want output for each one individually?

select count(age) as n, count(*) as n_total, calculated n / calculated n_total as pct_missing
from opera
union all
select count(sex) as n, count(*) as n_total, calculated n / calculated n_total as pct_missing
from opera

Here's a rough way to do this in PROC FREQ that my code then generalizes and summaries into a nice data set. You only need to know the name of the data set ahead of time.

Note that in this case it does not matter what types the variables are, ie character/numeric. This was used in the linked code.

Data OPERA_DIS_AGE;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage DPHYage AgAcq1st;

		do obs= 1 to 181308;

		         do i=1 to dim(vars);
				         vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
						 if vars(i) = '003' then call missing(vars(i));
						 
				 end;
				 output;
		end;
		drop i;
run;

*set input data set name;
%let INPUT_DSN = OPERA_DIS_AGE;
%let OUTPUT_DSN = want;
*create format for missing;

proc format;
    value $ missfmt ' '="Missing" other="Not Missing";
    value nmissfmt .="Missing" other="Not Missing";
run;

*Proc freq to count missing/non missing;
ods select all;
*turns off the output so the results do not get too messy;
ods table onewayfreqs=temp;

proc freq data=&INPUT_DSN.;
    table _all_ / missing;
    format _numeric_ nmissfmt. _character_ $missfmt.;
run;

And if all your variables are numeric, PROC MEANS will do it in one step with N/NMISS counts easily available.

proc means data=opera_dis_age n nmiss stackods;
ods output summary = want2;
run;

proc print data=want2;run;

@wlierman wrote:

I am still working / struggling with the code that produces a count of missing and non-missing observations.

I have been following this code from an earlier solution. This is not the entire code --just to the point where I hit a snag as shown in the log below.

Data OPERA.OPERA_DIS_AGE;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage DPHYage AgAcq1st;

		do obs= 1 to 181308;

		         do i=1 to dim(vars);
				         vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
						 if vars(i) = '003' then call missing(vars(i));
						 
				 end;
				 output;
		end;
		drop i;
run;





/* First1, get variable names */
proc transpose data = OPERA.OPERA_DIS_AGE(obs = 0) out = OPERA.vname;
    var _all_;
run;


/* then check if variable is all missing */
proc sql noprint;
   Select catx('','n(',_name_,') as', _name_) into :vnames separated by ','
   From OPERA.vname;
   Create table temp As
     Select &vnames from OPERA.OPERA_DIS_AGE;
quit;

Data OPERA.OPERA_DIS_AGE;
618          call streaminit(45);
619          length obs 8.;
620          array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage
620! DPHYage AgAcq1st;
621
622          do obs= 1 to 181308;
623
624                   do i=1 to dim(vars);
625                           vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
626                           if vars(i) = '003' then call missing(vars(i));
627
628                   end;
629                   output;
630          end;
631          drop i;
632  run;

NOTE: The data set OPERA.OPERA_DIS_AGE has 181308 observations and 11 variables.
NOTE: Compressing data set OPERA.OPERA_DIS_AGE increased size by 12.50 percent.
      Compressed is 126 pages; un-compressed would require 112 pages.
NOTE: DATA statement used (Total process time):
      real time           0.35 seconds
      cpu time            0.32 seconds


633  proc transpose data = OPERA.OPERA_DIS_AGE(obs = 0) out = OPERA.vname;
634      var _all_;
635  run;

NOTE: Numeric variables in the input data set will be converted to character in the output
      data set.
NOTE: Compression was disabled for data set OPERA.VNAME because compression overhead would
      increase the size of the data set.
NOTE: There were 0 observations read from the data set OPERA.OPERA_DIS_AGE.
NOTE: The data set OPERA.VNAME has 11 observations and 1 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


636  /* then check if variable is all missing */
637  proc sql noprint;
638     Select catx('','n(',_name_,') as', _name_) into :vnames separated by ','
639     From OPERA.vname;
640     Create table temp As
641       Select &vnames from OPERA.OPERA_DIS_AGE;
                         ----
                         22
                         76
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT,
              FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER,
              OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: Line generated by the macro variable "VNAMES".
1     n(obs) asobs,n(DEYEage) asDEYEage,n(DMHDage) asDMHDage,n(DCOMage) asDCOMage,n(DEARage)
             ------
             1    22
WARNING 1-322: Assuming the symbol AS was misspelled as asobs.

ERROR 22-322: Expecting a name.

1  !  n(obs) asobs,n(DEYEage) asDEYEage,n(DMHDage) asDMHDage,n(DCOMage) asDCOMage,n(DEARage)
                              ---------
                              22
1  ! asDEARage,n(DLEAage) asDLEAage,n(DDRSage) asDDRSage,n(DOUTage) asDOUTage,n(DREMage)
1  ! asDREMage,n(DPHYage) asDPHYage,n(AgAcq1st) asAgAcq1st
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <,
              <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE,
              LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

642  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Thank you for continuing help and assistance.

wlierman

View solution in original post

Reeza · Posted 05-07-2021 03:57 PM

Would you want output something like this?

If so, see the code here

Replace the CLASS data at the top with your input data set, Opera_DIS_AGE.

I'm not sure I understand how your code is supposed to work.

You do a transpose which gets you the list of variables (FYI - not really needed as you can use _numeric_ or _character_ in various places.)

But your SQL query is just a select of those variables? Were you intending to loop through each and do something like this?

select count(age) as n_age, count(*) as n_total, count(sex) as n_sex .....
from ...

Or did you want output for each one individually?

select count(age) as n, count(*) as n_total, calculated n / calculated n_total as pct_missing
from opera
union all
select count(sex) as n, count(*) as n_total, calculated n / calculated n_total as pct_missing
from opera

Here's a rough way to do this in PROC FREQ that my code then generalizes and summaries into a nice data set. You only need to know the name of the data set ahead of time.

Note that in this case it does not matter what types the variables are, ie character/numeric. This was used in the linked code.

Data OPERA_DIS_AGE;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage DPHYage AgAcq1st;

		do obs= 1 to 181308;

		         do i=1 to dim(vars);
				         vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
						 if vars(i) = '003' then call missing(vars(i));
						 
				 end;
				 output;
		end;
		drop i;
run;

*set input data set name;
%let INPUT_DSN = OPERA_DIS_AGE;
%let OUTPUT_DSN = want;
*create format for missing;

proc format;
    value $ missfmt ' '="Missing" other="Not Missing";
    value nmissfmt .="Missing" other="Not Missing";
run;

*Proc freq to count missing/non missing;
ods select all;
*turns off the output so the results do not get too messy;
ods table onewayfreqs=temp;

proc freq data=&INPUT_DSN.;
    table _all_ / missing;
    format _numeric_ nmissfmt. _character_ $missfmt.;
run;

And if all your variables are numeric, PROC MEANS will do it in one step with N/NMISS counts easily available.

proc means data=opera_dis_age n nmiss stackods;
ods output summary = want2;
run;

proc print data=want2;run;

@wlierman wrote:

I am still working / struggling with the code that produces a count of missing and non-missing observations.

I have been following this code from an earlier solution. This is not the entire code --just to the point where I hit a snag as shown in the log below.

Data OPERA.OPERA_DIS_AGE;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage DPHYage AgAcq1st;

		do obs= 1 to 181308;

		         do i=1 to dim(vars);
				         vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
						 if vars(i) = '003' then call missing(vars(i));
						 
				 end;
				 output;
		end;
		drop i;
run;





/* First1, get variable names */
proc transpose data = OPERA.OPERA_DIS_AGE(obs = 0) out = OPERA.vname;
    var _all_;
run;


/* then check if variable is all missing */
proc sql noprint;
   Select catx('','n(',_name_,') as', _name_) into :vnames separated by ','
   From OPERA.vname;
   Create table temp As
     Select &vnames from OPERA.OPERA_DIS_AGE;
quit;

Data OPERA.OPERA_DIS_AGE;
618          call streaminit(45);
619          length obs 8.;
620          array vars(*) $3. DEYEage DMHDage DCOMage DEARage DLEAage DDRSage DOUTage DREMage
620! DPHYage AgAcq1st;
621
622          do obs= 1 to 181308;
623
624                   do i=1 to dim(vars);
625                           vars(i)= put(rand('table', 0.4, 0.4, 0.2), Z3.);
626                           if vars(i) = '003' then call missing(vars(i));
627
628                   end;
629                   output;
630          end;
631          drop i;
632  run;

NOTE: The data set OPERA.OPERA_DIS_AGE has 181308 observations and 11 variables.
NOTE: Compressing data set OPERA.OPERA_DIS_AGE increased size by 12.50 percent.
      Compressed is 126 pages; un-compressed would require 112 pages.
NOTE: DATA statement used (Total process time):
      real time           0.35 seconds
      cpu time            0.32 seconds


633  proc transpose data = OPERA.OPERA_DIS_AGE(obs = 0) out = OPERA.vname;
634      var _all_;
635  run;

NOTE: Numeric variables in the input data set will be converted to character in the output
      data set.
NOTE: Compression was disabled for data set OPERA.VNAME because compression overhead would
      increase the size of the data set.
NOTE: There were 0 observations read from the data set OPERA.OPERA_DIS_AGE.
NOTE: The data set OPERA.VNAME has 11 observations and 1 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


636  /* then check if variable is all missing */
637  proc sql noprint;
638     Select catx('','n(',_name_,') as', _name_) into :vnames separated by ','
639     From OPERA.vname;
640     Create table temp As
641       Select &vnames from OPERA.OPERA_DIS_AGE;
                         ----
                         22
                         76
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT,
              FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER,
              OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: Line generated by the macro variable "VNAMES".
1     n(obs) asobs,n(DEYEage) asDEYEage,n(DMHDage) asDMHDage,n(DCOMage) asDCOMage,n(DEARage)
             ------
             1    22
WARNING 1-322: Assuming the symbol AS was misspelled as asobs.

ERROR 22-322: Expecting a name.

1  !  n(obs) asobs,n(DEYEage) asDEYEage,n(DMHDage) asDMHDage,n(DCOMage) asDCOMage,n(DEARage)
                              ---------
                              22
1  ! asDEARage,n(DLEAage) asDLEAage,n(DDRSage) asDDRSage,n(DOUTage) asDOUTage,n(DREMage)
1  ! asDREMage,n(DPHYage) asDPHYage,n(AgAcq1st) asAgAcq1st
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <,
              <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE,
              LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

642  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Thank you for continuing help and assistance.

wlierman

wlierman · Posted 05-07-2021 05:20 PM

Thank you for taking the time to address this problem.

I really appreciate your help.

Thanks.

wlierman

Ksharp · Posted 05-08-2021 08:51 AM

Should padding a white blank :

Select catx('',
-->
Select catx(' ',

Counting number of missing data

Re: Counting number of missing data

Re: Counting number of missing data

Re: Counting number of missing data

Re: Counting number of missing data

Counting number of missing data

Re: Counting number of missing data

Re: Counting number of missing data

Re: Counting number of missing data

Re: Counting number of missing data

Missed SAS Innovate in Orlando?

SAS Training: Just a Click Away