topic Re: Adding New Missing Observation in Data Step in SAS Enterprise Guide

Adding New Missing Observation in Data Step

narulap — Fri, 16 Mar 2018 13:35:45 GMT

I have multiple Data sets of missing and not missing observations. In some Data sets I have 1 observation of 100% Not Missing and want to add another Observation of MISSING 0%. I am able to do it in numeric variables but for numeric it is adding the observation but with the Not Missing value.

May be the issue is with format. that I am adding new observation in place of format.

I have the PROC format for the Character and Numeric Variables for Missing and Not Missing values.

This is the proc format :

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

This is the code which I use to add Observation into multiple tables.

	proc sql;
			select count("&var.") into: n
			from Final_Output_&var;
	run;
		       %put Nobs=&n.;
		
		%if &n.=1 %then %do;
		   data final_output_&var;
				set final_output_&var end=eof;
				output;
				if eof then do; 
				&var. = "MISSING";
				COUNT=0;
				PERCENT=0;
				output; /* Output new observation */  	
				end;
		  run;					
		%end;

It gets Inserted correctly under Numeric Variable but It

But for the Character its is not getting inserted as shown below:

Re: Adding New Missing Observation in Data Step

Kurt_Bremser — Fri, 16 Mar 2018 13:37:34 GMT

Please provide example data in a data step with datalines.

Re: Adding New Missing Observation in Data Step

ballardw — Fri, 16 Mar 2018 15:17:44 GMT

Warning that habitual use of structures like:
data final_output_&var;
set final_output_&var end=eof;

will create hard to diagnose data issues because you destroy the old version.

Since you do not show use of the format or data with the format it is hard to say. I might try

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

with the format.

It looks like you may be doing a lot of work to duplicate the results of one or more SAS report or summarization procedures.

Re: Adding New Missing Observation in Data Step

narulap — Fri, 16 Mar 2018 16:03:53 GMT

%macro abc() ;

	/*1. Create Data Sets within the defined timeframe*/
	proc sql;
		create table MV_MISSING_VALUE AS
		select *
		from pankaj.mv_yield_detail_mm
		where "&mindate."dt <= PROCESSED_DTTM <="&maxdate."dt;
	quit;
	
	/*2. Generate the table with proc sql with varname and type*/
	
	proc sql ;
		create table varlist_1 as
		select name, type from  dictionary.columns
		where libname = upcase("work") and memname = upcase("MV_MISSING_VALUE");
	quit;

	/*3. Looping through the Variables and checking the Data Type */
 	%do I=1 %to %sysfunc(countw(&varlist)); 
	 		%let var = %scan(&varlist, &I);
			%put &var.;

			/*3.1To check the Data Type for all the varibales in varlist. */		
			proc sql noprint;
				select type into: t separated by ' ' from varlist_1
				where name="&var.";
			quit;
			%put check=&t.;


	/*4. Formatting with Character Values*/
	%if &t=char %then %do;
		/*length var $32;*/
		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;

		/*DATA Step for Filtering Only Missing Values in a separate Tables*/
		DATA Final_Output_&var;
			set Final_Output_&var;
			where put (&var., $missfmt.) in ("Missing");
		run;		
	%end; 

	/*5. Formatting with Numeric Format*/
	%else %do;
		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;
	
		/*DATA Step for Filtering Only Missing Values in a separate Tables*/
		DATA Final_Output_&var;
			set Final_Output_&var;
			where put (&var., missfmt.) in ("Missing");
		run;
		
	%end;

	
	/*6. Add new variable*/
	data Final_Output_&var ; 
			set Final_Output_&var ;
			VARNAME = "&var" ;
			
		run ;


	%end;
%mend;
%abc;

This is what I am intended to do in One Macro. I have attached the sample data.

At last, I want the Missing % along with the Variable name.

Re: Adding New Missing Observation in Data Step

Kurt_Bremser — Fri, 16 Mar 2018 23:34:58 GMT

Please post example data in a data step with datalines.

Spreadsheets are mostly useless. They do not convey column attributes, and many of us don't open them for security reasons. Many organisations block their download.

Re: Adding New Missing Observation in Data Step

Patrick — Sat, 17 Mar 2018 01:06:06 GMT

@narulap

Because many people here in this forum don't want to download Excel files (security concerns) a really quick and easy way for you to create sample data from an Excel file is to use EG with the import wizard, select to include the data and then slightly modify the resulting generated data step. I've done this now for you.

DATA sample;
    INFILE DATALINES4
        DLM='|'
        truncover
        DSD ;
    FORMAT
        SHIFT_CURING_DATE DATE9.
        SHIFT_BUILD_DATE DATE9.
        PROCESSED_DTTM   DATETIME22.3 ;

    INPUT
        BATCH_ID         : $7.
        TREAD            : $5.
        TIRESIZE         : $3.
        CUSTOMER         : $3.
        CUSTOMER_INTENDED : $5.
        ARTICLE7         : $6.
        TIRE_TYPE        : $2.
        WAREHOUSED       : BEST32.
        TBM_STAGE1       : $5.
        TBM_STAGE2       : $1.
        CURING_STAGE1    : $5.
        BLADDER          : $6.
        MOLD             : $7.
        SHIFT_CURING_DATE : BEST32.
        SHIFT_BUILD_DATE : BEST32.
        PROCESSED_DTTM   : BEST32. ;
DATALINES4;
1234567|7480Z| |abc|Trade|123456|RE|.|TBA21| |CUFA0|CUAGDF|M410946|21211|21211|1832722557
1234568|38128|225|abc|Trade|123457|RE|.|TB201| |CUE18|CUAGDF|M415224|21196|21196|1831425644
1234569|37371|226|abc|Trade|123458|RE|.|TB214| |CUG07|CUAGDF|M412290|21187|21187|1830649694
1234570|7047|227|abc|Ford|123459|OE|.|TB302| |CUC19|CUAGDF|M411967|21200|21200|1831768923
1234571|YWL| |abc|Trade|123460|RE|.|TB309| |CUJ03|CUAGDF|M413836|21200|21198|1831735814
1234572|38072|264|abc|Trade|123461|RE|.|TB217| |CUG15|CUAGDF|M417268|21189|21184|1830831636
1234573|7372Z|265| |Trade|123462|RE|.|TBA21| |CUCB0|CUAGDF|M412572|21186|21186|1830540294
1234574|OPB|266|abc|Trade|123463|RE|1|TB217| |CUI42|CUAGDF|M404461|21191|21191|1830969455
1234575|YLO|267|abc|Trade|123464|RE|1|TB215| |CUG01|CUAGDF|M412529|21211|21211|1832723202
1234576|37428|268|abc|Trade|123465|RE|1|TB213| |CUA03|CUAGDF|M408985|21194|21193|1831220366
1234577|38072|269|abc|Trade|123466|RE|1|TB217| |CUI37|CUAGDF|M417266|21198|21198|1831603331
;;;;
run;

The macro you've posted is not fully working with the data you've posted - so that's may be something else you want to test next time before posting code together with data (unless your problem is to make the code work without errors).

I'm not fully clear what you're actually trying to achieve: Just create reports or collect some stats about missings in a SAS table.

Always try to keep things simple and make it work with simple code before you start wrapping macro code around. And also search the forums here first as often someone else had already a similar question and there is a solution around.

The following solution might give you some ideas what you could be doing:

https://communities.sas.com/t5/General-SAS-Programming/How-to-count-non-missing-rows-for-all-columns-and-store-results/m-p/403982/highlight/true#M50771

Re: Adding New Missing Observation in Data Step

narulap — Sat, 17 Mar 2018 12:17:36 GMT

Okay, I am new to this community. It will not be uploaded next time. Thanks for sharing the info.

Re: Adding New Missing Observation in Data Step

narulap — Mon, 19 Mar 2018 15:20:10 GMT

Thank you @Patrick for being so kind. I am new to this community and for sure will not attach the files next time 🙂

Re: Adding New Missing Observation in Data Step

narulap — Mon, 26 Mar 2018 11:12:48 GMT

Thank you all for your valuable inputs. I have changed the code and everything is running in a single macro which was my assignment.