How do I get the character and numeric values in macro for missing and not missing values in SAS EG

[narulap]

Hello, I am trying to get the missing and not missing values for 17 variables out of which 4 are Numeric.

Belos is the proc format which I have used:

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

 %do I=1 %to %sysfunc(countw(&varlist)); 
 	%let var = %scan(&varlist, &I);
	%put &var.;
	

	proc freq data=work.MV_MISSING_VALUE;
	
	format &var. missfmt. ; /* apply format for the duration of this PROC */
	tables &var. / missing missprint nocum  out= Final_Output_&var ;

	run;

ERROR: You are trying to use the numeric format MISSFMT with the character variable TIRE_TYPE in data set WORK.MV_MISSING_VALUE.

Is there any way to edit the format statement in PROC FREQ above to get the numeric as well character values. I understood the error but not able to apply the correct syntax or do I need to change the numeric variable to character and then proceed?

Look forward to your reply!!

[Kurt_Bremser]

Derive a list of variables from metadata and automate with call execute:

%let lib=work;
%let dataset=MV_MISSING_VALUE;

proc sql;
create table varlist as
select name, type from dictionary.columns
where libname = upcase("&lib.") and memname = upcase("&dataset");
quit;

%macro checkmiss(var,type);
proc freq data=&lib..&dataset;
%if &type=char %then %do;
format &var. $missfmt. ; /* apply format for the duration of this PROC */
%end; %else %do;
format &var. missfmt. ; /* apply format for the duration of this PROC */
%end;
tables &var. / missing missprint nocum  out= Final_Output_&var ;
run;
%mend;

data _null_;
set varlist;
call execute('%nrstr(%checkmiss(' !! trim(name) !! ',' !! trim(type) !! '));');
run;

[narulap]

I am having the "varlist" which has the variables list. I am trying to run the code in a macro. Below is the full code which I am using.

/*Assign Macros for Minimum Date and Maximum Date-1st STEP*/
%let mindate = '01JAN2018:00:00:00';
%put &mindate;
%let maxdate = '31JAN2018:00:00:00';
%put &maxdate;

/*Using Macros(Minimum and Maximum date) in PROC SQL-2nd Step */

/* create a format to group missing and nonmissing */
/* Format is necessary to get the final Output in Missing and Not Missing Format-3rd Step*/

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


%let varlist = TBM_STAGE1 TBM_STAGE2 SHIFT_BUILD_DATE BLADDER CURING_STAGE1 SHIFT_CURING_DATE MOLD ARTICLE7 CUSTOMER CUSTOMER_INTENDED 
TIRESIZE TIRE_TYPE TREAD WAREHOUSED BATCH_ID Processed_dttm;

%macro abc() ;

	proc sql;
	create table MV_MISSING_VALUE AS
	select *
	from pankaj.mv_yield_detail_mm
	where "&mindate."dt <= PROCESSED_DTTM <="&maxdate."dt
	ORDER BY PROCESSED_DTTM;
	quit;

	data _null_;
	set MV_MISSING_VALUE;
	/*call execute('%nrstr(%abc(' !! trim(varlist) !! ',' !! trim(varlist) !! '));');*/
	run;

 %do I=1 %to %sysfunc(countw(&varlist)); 
 	%let var = %scan(&varlist, &I);
	%put &var.;
	
	proc freq data=work.MV_MISSING_VALUE;
	%if &varlist=char %then %do;
		format &var. missfmt. ; /* apply format for the duration of this PROC */
	%end; %else %do;
	format &var. $missfmt. ; /* apply format for the duration of this PROC */
	%end;
	tables &var. / missing missprint nocum  out= Final_Output_&var ;
	run;
%end;
%mend;
%abc;

[Kurt_Bremser · Accepted solution]

You misunderstand what my code does. I only have a small macro so that I can easier call the proc freq in call execute. I could write the complete proc freq step into the call execute (and do the numeric/character logic in data step code), without any macro at all.

See this:

/*Assign Macros for Minimum Date and Maximum Date-1st STEP*/
%let mindate = '01JAN2018:00:00:00';
%put &mindate;
%let maxdate = '31JAN2018:00:00:00';
%put &maxdate;

/*Using Macros(Minimum and Maximum date) in PROC SQL-2nd Step */

/* create a format to group missing and nonmissing */
/* Format is necessary to get the final Output in Missing and Not Missing Format-3rd Step*/

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

proc sql;
create table MV_MISSING_VALUE AS
select *
from pankaj.mv_yield_detail_mm
where "&mindate."dt <= PROCESSED_DTTM <="&maxdate."dt
ORDER BY PROCESSED_DTTM;
quit;

%let lib=work;
%let dataset=MV_MISSING_VALUE;

proc sql;
create table varlist as
select name, type from dictionary.columns
where libname = upcase("&lib.") and memname = upcase("&dataset");
quit;

%macro checkmiss(var,type);
proc freq data=&lib..&dataset;
%if &type=char %then %do;
format &var. $missfmt. ; /* apply format for the duration of this PROC */
%end; %else %do;
format &var. missfmt. ; /* apply format for the duration of this PROC */
%end;
tables &var. / missing missprint nocum  out= Final_Output_&var ;
run;
%mend;

data _null_;
set varlist;
call execute('%nrstr(%checkmiss(' !! trim(name) !! ',' !! trim(type) !! '));');
run;

[narulap]

Thanks Kurt, 

I understood the code which you provided and have implemented the same logic in my code and its working fine. As I am new to SAS EG and I was given a requirement straight away to point out the missing and non missing values.

This is how I implemented and its working fine.

	proc sql ;
		create table varlist_1 as
		select name, type from  dictionary.columns
		where libname = upcase("work") and memname = upcase("MV_MISSING_VALUE");
	quit;


 %do I=1 %to %sysfunc(countw(&varlist)); 
 	%let var = %scan(&varlist, &I);
	%put &var.;


	proc sql noprint;
	select type into: t separated by ' ' from varlist_1
	where name="&var.";
	quit;
	%put check=&t.;

Thanks for your support!!!

[Tom]

Looks like you are working way too hard for this.

Can't you just let SAS handle this by using variable lists in the format statement?

format _numeric_ missfmt. _character_ $missfmt. ;

You might need to add another format statement at some point to remove the MISSFMT. from any statistics you calculated.

[narulap]

Yes, its true. Finally, i got the required output. 

Thanks for your input Tom.

[RW9]

Or just use the inbuilt types and have two procs:

proc freq data=work.mv_missing_value;
  tables _numeric_ / missing misprint nocum out=numeric_missing;
  format _all_ missfmt.;
run;
proc freq data=work.mv_missing_value;
  tables _character_ / missing misprint nocum out=char_missing;
  format _all_ $missfmt.;
run;

[narulap]

Hi, I already had the two proc freq for numeric and character but I am trying to use a Macro for that and run the whole in a macro.

I already had the output for all the characters variables and struggling for numeric variables.

Bladder            Count           Frequency

Missing            913               0.101776235
Not Missing     896153         99.898223765

This is the code from where I started :

/*Assign Macros for Minimum Date and Maximum Date-1st STEP*/
%let mindate = '01JAN2018:00:00:00';
%put &mindate;
%let maxdate = '31JAN2018:00:00:00';
%put &maxdate;

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

%let varlist = TBM_STAGE1 TBM_STAGE2 SHIFT_BUILD_DATE BLADDER CURING_STAGE1 SHIFT_CURING_DATE MOLD ARTICLE7 CUSTOMER CUSTOMER_INTENDED 
TIRESIZE TIRE_TYPE TREAD WAREHOUSED BATCH_ID Processed_dttm;

%macro abc() ;

	proc sql;
	create table MV_MISSING_VALUE AS
	select *
	from pankaj.mv_yield_detail_mm
	where "&mindate."dt <= PROCESSED_DTTM <="&maxdate."dt
	ORDER BY PROCESSED_DTTM;
	quit;
	
 %do I=1 %to %sysfunc(countw(&varlist)); 
 	%let var = %scan(&varlist, &I);
	%put &var.;
	
	proc freq data=work.MV_MISSING_VALUE;
	format &var. $missfmt.; /* apply format for the duration of this PROC */
	tables &var. / missing missprint nocum  out= Final_Output_&var ;
	run;
%end;
%mend;
%abc;

[RW9]

Sorry, just not seeing it.  Why are you trying to do data processing in macro?  Macro is only used for generating some text?  What you are effectively doing with that is generating a proc freq for every single variable, each time that is a load/write/process overhead.  I just don't see what the macro is adding to the process here, as its certainly coming at a large overhead.

[Patrick]

In case you just need a report below could do

data have;
  set sashelp.class;

  if mod(_n_,2)=0 then
    call missing(name);

  if mod(_n_,3)=0 then
    call missing(age);
run;

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

data cldata;
  set have(obs=1);
  call missing(of _all_);
  output;
run;

options missing='0';
proc tabulate data=have missing classdata=cldata;
  format _numeric_ missfmt. _character_ $missfmt.; 
  class _numeric_ _character_;
  keylabel n=' ';
  table _numeric_ _character_;
run;
options missing='.';

[Tom]

You can also just let PROC SUMMARY do the work for you in a single pass. 

%let in=test;
%let out=want;

proc means stackodsoutput data=&in noprint chartype ;
  class _all_ / missing ;
  ways 1 ;
 format _numeric_ missfmt. _character_ $missfmt. ;
  output out=stats ;
run;

You will need to add a little logic to clean up the output.

proc transpose data=stats(obs=0) out=names ;
 var _all_;
run;

data &out ;
  length varnum point 8 _name_ $32 n nmiss 8 ;
  retain varnum point _name_ n nmiss ;
  set stats ;
  by _type_ ;
  if first._type_ then do;
     if _n_=1 then varnum=length(_type_);
     else varnum=varnum-1;
     point = varnum ;
     n=0;
     nmiss=0;
     set names (keep=_name_) point=point;
  end;
  if vvaluex(_name_) ='Missing' then nmiss=_freq_;
  else n=_freq_;
  if last._type_;
  keep varnum _name_ n nmiss ;
run;
proc sort data=&out;
  by varnum ;
run;