topic Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data in SAS Programming

Thank you Reeza, Tom, Kurt How to count missing and non-missing data

wlierman — Wed, 05 May 2021 19:47:08 GMT

Thanks to you all.

Once I added all the code that Reeza originally sent the code worked well.

The entire code is shown.

What literature in the SAS materials would I go to cover what the code does in this problem?

Thank you.

wklierman

Data OPERA.OPERA_All_2;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DLEAdi DMHDdi DCOMdi DEARdi DEYEdi DDRSdi DOUTdi DREMdi DPHYdi;

		do obs= 1 to 200; *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.OPERA_All_2;
%let OUTPUT_DSN = OPERA.OPERA_All_4;
* 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 none;
* turns off the output so the results sdo not get too messy;
ods table onewayfreqs=temp;

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

ods select all;
* Format output;   

Data OPERA.OPERA_ALL_2A;
   length variable $32.  variable_value  $50.;
   set temp;
   Variable=scan(table, 2);
   Variable_Value=strip(trim(vvaluex(variable)));
   presentation = catt(frequency, " (", trim(put(percent/100, percent7.1)), ")");
   keep variable variable_value frequency percent cum: presentation;
   label variable='Variable' variable_value='Variable_Value';
run;

proc sort Data = OPERA.OPERA_ALL_2A;
   by Variable;
run;

* make a wide data set for presentation, with values a N (Percent);

proc transpose data = OPERA.OPERA_All_2A  out = OPERA.OPERA_All_3_wide   (drop=_name_);  /*(rename=(_name_ = Code  col1=Value));*/
      by variable;  *obs;
	  id variable_value;
      var presentation;  *DLEAdi--DPHYdi;
run;

* transpose only N;

proc transpose data=OPERA.OPERA_All_2A  out = OPERA.OPERA_All_3_wide_N prefix=N_;
     by variable;  
	  id variable_value;
      var presentation;  
run;


* transpose only percents;

proc transpose data=OPERA.OPERA_All_2A  out = OPERA.OPERA_All_3_wide_PCT  prefix=PCT_;
     by variable;  
	 id variable_value;
     var presentation;  
run;


* final output file;

data &Output_DSN.;
   Merge OPERA.OPERA_All_3_wide_N  OPERA.OPERA_All_3_wide_PCT OPERA.OPERA_All_3_wide;
     by variable;
	 drop _name_;
	 label N_Missing='# Missing'  N_Not_Missing='# Not Missing'
	     PCT_Missing='% Missing'  N_Not_Missing='% Not Missing'
		 Missing='Missing'
		 Not_Missing='Not Missing';
run;

title "Missing Report of  &INPUT_DSN.";

proc print data=&Output_DSN. noobs label;
run;

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

ballardw — Wed, 05 May 2021 22:29:40 GMT

Likely start with the documentation for SAS data step functions. I'm not sure which ones you aren't familiar with thous VVALUEX is likely one you haven't seen. Array processing. Proc Transpose.

Start at https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=pgmsashome&docsetTarget=home.htm&locale=en

Search for any of the functions or statements you aren't familiar with.

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Kurt_Bremser — Thu, 06 May 2021 07:37:34 GMT

Maxim 1. So start here:

Programming Documentation for SAS® 9.4 and SAS® Viya®

From there,

Functions and CALL Routines

DATA Step Statements

and the procedures, e.g.

FORMAT Procedure

TRANSPOSE Procedure

FREQ Procedure (which is listed under "Base SAS Statistical Procedures")

ODS is listed under "Output and Graphics"

Further, make use of Maxim 6 (Google Is Your Friend). I have found that the search engine has become very good at answering questions, like "how do i create html with sas ods?". Such questions (always include the keyword "sas") quickly lead you to the relevant documentation, postings here on the community, and papers from the many SAS conventions. Especially the papers provide "how to" cookbooks.

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Ksharp — Thu, 06 May 2021 12:29:56 GMT

This my paper could give you a start .

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/4737-2020.pdf

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

wlierman — Thu, 06 May 2021 14:38:56 GMT

Thank you for sending the link to your paper.

Appreciate ti.

wklierman

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

wlierman — Thu, 06 May 2021 15:24:57 GMT

Hi KSharp,
Great paper.
Thank you.
wlierman

Re: checking on missing| non-missing variables

wlierman — Thu, 06 May 2021 22:01:52 GMT

This is a continuation of sorts on the topic of determining the missing / nonmissing variables in a data set. I am following a code algorithm from the 2020 Global Forum paper by Shan and Bremser (great paper by the way).

PROC SQL 
This will be faster for big tables; it will pick up numeric and character variables: 
/* firstl, get variable names */
proc transpose data=have(obs=0) out=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 vname;
create table temp as
select &vnames from have;
quit;
proc transpose data=temp out=want;
var _all_;
run;
proc sql noprint;
select _name_ into : missing_variables separated by ' '
from want
where col1=0;
quit;
 
%put Missing variables are : &missing_variables ;

Anyway, this is my code and the accompanying log which specifies the error that I am getting:

/* First1, get variable names */
proc transpose data = OPERA.OPERA_dis_age(obs = 0) out = OPERA.OPERA_vnames;
    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.OPERA_vnames;
   Create table temp As
     Select &vnames 
     From OPERA.OPERA_dis_age;
quit;

And the log

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


66   proc sql noprint;
67      Select catx(' ','n('_name_,') as',_name_) into :vnames separated by ','
                        ----------
                        49  22
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <,
              <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE,
              LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

68      From OPERA.OPERA_vnames;
69      Create table temp As
70        Select &vnames
WARNING: Apparent symbolic reference VNAMES not resolved.
70        Select &vnames
                 -
                 22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT,
              SUBSTRING, USER.

71        From OPERA.OPERA_dis_age;
72   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

What is the problem here? I checked over the code but could have missed something.

Thanks for your help.

wlierman

Re: checking on missing| non-missing variables

Ksharp — Fri, 07 May 2021 12:27:37 GMT

You are missing a comma . Compare to my original code.
Select catx(' ','n('_name_,') as',_name_) into :vnames separated by ','

-->
Select catx(' ','n(' , _name_,') as',_name_) into :vnames separated by ','

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Ksharp — Fri, 07 May 2021 12:31:28 GMT

Data OPERA_All_2;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DLEAdi DMHDdi DCOMdi DEARdi DEYEdi DDRSdi DOUTdi DREMdi DPHYdi;

		do obs= 1 to 200; *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;



/* firstl, get variable names */
proc transpose data=OPERA_All_2(obs=0) out=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 vname;
create table temp as
select &vnames from OPERA_All_2;
quit;
proc transpose data=temp out=want;
var _all_;
run;
proc sql noprint;
select _name_ into : missing_variables separated by ' '
from want
where col1=0;
quit;
 
%put Missing variables are : &missing_variables ;

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Ksharp — Fri, 07 May 2021 12:55:47 GMT

If you want a report include n of missing ,percent of missing ......... Try this one :

Data OPERA_All_2;
        call streaminit(45);
		length obs 8.;
		array vars(*) $3. DLEAdi DMHDdi DCOMdi DEARdi DEYEdi DDRSdi DOUTdi DREMdi DPHYdi;

		do obs= 1 to 200; *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;




%macro report(dsn=,vname=);
proc sql ;
create table temp as
select "&vname." as vname length=40
      ,n(&vname.) as n_not_missing label='# Not Missing' 
      ,n(&vname.)/count(*) as pct_not_missing label='% Not Missing' format=percent8.2
      ,nmiss(&vname.) as n_missing label='# Missing' 
      ,nmiss(&vname.)/count(*) as pct_missing label='% Missing'  format=percent8.2
from  &dsn ;
quit;
proc append base=want data=temp force;run;
%mend;




proc transpose data=OPERA_All_2(obs=0) out=vname;
var _all_;
run;
proc delete data=want;run;
data _null_;
 set vname;
 call execute(cats('%report(dsn=OPERA_All_2,vname=',_name_,')'));
run;

Re: checking on missing| non-missing variables

wlierman — Fri, 07 May 2021 14:50:02 GMT

Thank you very much.

wlierman

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

wlierman — Fri, 07 May 2021 14:51:33 GMT

Thank you for the code.

I will do that.

wlierman

topic Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data in SAS Programming

**** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: checking on missing| non-missing variables

Re: checking on missing| non-missing variables

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

Re: checking on missing| non-missing variables

Re: **** Thank you **** Reeza, Tom, Kurt How to count missing and non-missing data

topic Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data in SAS Programming

Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data

Re: Thank you Reeza, Tom, Kurt How to count missing and non-missing data