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

 

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!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

11 REPLIES 11
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

 

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

Thanks for your input Tom.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

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
Super User Tom
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2357 views
  • 3 likes
  • 5 in conversation