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:
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.
Please provide example data in a data step with datalines.
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.
%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.
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.
Okay, I am new to this community. It will not be uploaded next time. Thanks for sharing the info.
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:
Thank you @Patrick for being so kind. I am new to this community and for sure will not attach the files next time 🙂
Thank you all for your valuable inputs. I have changed the code and everything is running in a single macro which was my assignment.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: