excluding missing values on proc tabulate

[sheren_deep1]

Hi,

I need help to omit missing values on proc tabulate procedure. I understand that by right proc tabulate do not calculate missing values. However, when I i do not specify /missing on the class variable.

class DISTRICT NEW_ETHNICITY NEW_CITIZENSHIP / missing ;

The result won't appear. 

Here is the complete SAS codes. 

	TITLE j=left "Jadual B1 : Jumlah penduduk mengikut kumpulan etnik, kawasan pihak berkuasa tempa'tan dan negeri, Malaysia, 2010";
	Title2 font=bold italic j=left "Table   B1 : Total population by ethnic group, local authority area and state, Malaysia, 2010";
	title3 " ";
	title4 j=left "Negeri : NEGERI SEMBILAN";
	title5 font=bold italic j=left "State";
	ods escapechar='^';
	
	proc tabulate data=WORK.DOSM order=data missing;
			class DISTRICT NEW_ETHNICITY NEW_CITIZENSHIP / missing ;
		classlev NEW_ETHNICITY;
		CLASSLEV NEW_CITIZENSHIP;
		classlev district;
		var ID;
		
		table ALL DISTRICT=' ', ALL 
			NEW_ETHNICITY='Warganegara Malaysia ^S={font_style=italic}
	
	/ Malaysian Citizens'   NEW_CITIZENSHIP='Bukan Warganegara Malaysia ^S={font_style=italic}/*Non-   Malaysian citizens *' 
			/Box='Daerah Pentadbiran/Kawasan Pihak Berkuasa Tempatan ^S={font_style=italic}
	
													Administrative District/Local Authority Area' ;
		keylabel N=' ';
		keylabel all='Jumlah ^S={font_style=italic}/ Total';
		footnote font=arial bold j=left "Nota" font=arial bold italic "/Note:";
		
		footnote2 j=l f='ARIAL amt/bold' "^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^Angka-angka di atas tidak disesuaikan untuk kurang penghitungan.";
		footnote3 j=l font=bold italic "^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^The above figures have not been adjusted for under enumeration.";
	run;

And this is the result that I obtain. 

But, I need the result to not show the missing values as per the above red highlighted columns.  

Could anyone please help me on this? 

Thank You. 

[Reeza]

You can have multiple CLASS statements. Include the missing only on the variables needed.

[sheren_deep1]

Hi, 

I tried using the multiple class statement but still it wont give me the output without me putting missing on all variables. 

[PGStats]

Remove the missing option from the proc tabulate statement and the missing option from the class statement. You should get a table with all mon missing class levels. If a table is not output, check the log for errors.

[sheren_deep1]

Hi, 

Here is the log warning 

WARNING: A class, frequency, or weight variable is missing on every observation. 

And the output won't appear as well. 

[PGStats]

Could it be the district that's missing everywhere?

[sheren_deep1]

Hi, 

Actually the missing values are in the NEW_ETHNICITY and NEW_CITIZENSHIP variables 

[ballardw]

---

@sheren_deep1 wrote:

Hi, 

Actually the missing values are in the NEW_ETHNICITY and NEW_CITIZENSHIP variables 

---

It is hard to tell what you may actually when you have two variable with apparently LOTS of missing values.

Perhaps you want to investigate the use of a CLASSDATA data set with the EXCLUSIVE option.

Or may be presummarize the data prior to display with Tabulate or possibly proc print;

Proc summary data=WORK.DOSM ;
   class DISTRICT NEW_ETHNICITY NEW_CITIZEN;
   output out=dosmsummary ;
run;

would create a data set with counts in the _freq_ variable of the combinations of the variables that actually exist. The _type_ indicates which combinations are represented.

[Reeza]

That's a lot of missing records. What about ID? Is that missing, since it's what you're using as your VAR? 

---

@sheren_deep1 wrote:

Hi, 

Actually the missing values are in the NEW_ETHNICITY and NEW_CITIZENSHIP variables 

---

I think at this point you need to post some sample data so we can replicate the issues. I know that you likely can't share the actual data, but please make fake data that mimics your issue.

[sheren_deep1]

Hi, 

There is no missing values on the ID. 

[Reeza]

If you can’t provide data, debug step by step. Add one variable at a time to build your table until it fails and then what you last changed is your issue.

[PGStats]

Here is another idea:

Try removing the MISSING options and adding the statement

where cmiss(DISTRICT, NEW_ETHNICITY, NEW_CITIZENSHIP) = 0;

to exclude obs with any missing values.

[sheren_deep1]

Hi, 

First of all, I would like to thank you for showing your interest. I have been struggling with this one for days. 

I have attached the sample data here. Also, here is the data steps for the dataset. 

%web_drop_table(WORK.DOSM);
FILENAME REFFILE '/home/sheren_deep1/DOSM/FACT_BPPD_POPULATION05_Seremban1.csv';

PROC IMPORT DATAFILE=REFFILE DBMS=CSV OUT=WORK.DOSM;
	GETNAMES=YES;
RUN;



data WORK.DOSM;
	%let _EFIERR_ = 0;

	/* set the ERROR detection macro variable */
	infile REFFILE delimiter=',' MISSOVER DSD firstobs=2;
	informat ID best32.;
	informat BPPD_STATE_SUB_DESC_M $100.;
	informat BPPD_ADM_DISTRICT_SUB_DESC_M $100.;
	informat BPPD_POP_ETHNIC_SUB_DESC_M $100.;
	informat BPPD_POP_COUNTRY_NONCITIZEN_SUB_ $100.;
	format ID best12.;
	format BPPD_STATE_SUB_DESC_M $100.;
	format BPPD_ADM_DISTRICT_SUB_DESC_M $100.;
	format BPPD_POP_ETHNIC_SUB_DESC_M $100.;
	format BPPD_POP_COUNTRY_NONCITIZEN_SUB_ $100.;
	input ID BPPD_STATE_SUB_DESC_M  $
         BPPD_ADM_DISTRICT_SUB_DESC_M  $
        BPPD_POP_ETHNIC_SUB_DESC_M  $
       BPPD_POP_COUNTRY_NONCITIZEN_SUB_  $;

	if _ERROR_ then
		call symputx('_EFIERR_', 1);

	/* set ERROR detection macro variable */
run;

PROC CONTENTS DATA=WORK.DOSM;
RUN;

%web_open_table(WORK.DOSM);

ods escapechar='^'; 
data work.DOSM;
	set work.dosm (rename=(BPPD_STATE_SUB_DESC_M=STATE 
		BPPD_POP_COUNTRY_NONCITIZEN_SUB_=CITIZENSHIP 
		BPPD_POP_ETHNIC_SUB_DESC_M=ETHNICITY BPPD_ADM_DISTRICT_SUB_DESC_M=DISTRICT));
	length NEW_ETHNICITY $100.;
length NEW_CITIZENSHIP $100.;
	if CITIZENSHIP in ('Valid Blank (Warganegara Malaysia sahaja)' , 
		'Valid blank') and ETHNICITY in ('Afrika' , 'Amerika Syarikat', 'Arab', 
		'Australia', 'Bangladesh', 'Bangsa Asia', 'Bangsa Asia Lain', 'Bangsa 
		Eropah' 'Bangsa Eropah Lain', 'Denmark', 'Filipina', 'Indonesia', 
		'Jepun', 'Jerman', 'Korea'	, 'Lain-lain', 'Lain-lain Bangsa', 'Myanmar', 
		'Negrito', 'Nepal', 'New Zealand', 'Pakistan', 'Perancis', 'Sepanyol', 
		'Thai', 'Thailand', 'United Kingdom', 'Vietnam', 'Serani', 'Sian', 'Tanjong') 
		then
			NEW_ETHNICITY='Lain-Lain ^S={font_style=italic} / Others';
	Else IF CITIZENSHIP in ('Valid Blank (Warganegara Malaysia sahaja)' , 
		'Valid blank') and ETHNICITY in ('India' , 'Indian Muslim', 
		'Orang India Lain', 'Punjabi (kecuali Sikh)', 'Sikh', 'Sinhala', 
		'Tamil India', 'Tamil 
		Sri Lanka' 'Telegu' 'Telugu', 'Malayali') then
			NEW_ETHNICITY='India ^S={font_style=italic} / Indians';
	ELSE IF CITIZENSHIP IN ('Valid Blank (Warganegara Malaysia sahaja)' , 
		'Valid blank') and ETHNICITY in ('Foochow', 'Foochow / Hokchiu' , 'Hainan', 
		'Kantonis', 'Henghua', 'Hokchia', 'Hokchiu', 'Hokkien', 'Khek (Hakka)', 
		'Kwongsai', 'Orang Cina Lain', 'Teochew') THEN
			NEW_ETHNICITY='Cina ^S={font_style=italic} / Chinese';
	else IF CITIZENSHIP IN ('Valid Blank (Warganegara Malaysia sahaja)' , 
		'Valid blank') and ETHNICITY in ('Bajau', 'Balabak', 'Bidayuh' 
		'Bidayuh / Dayak Darat', 'Bisaya', 'Bulongan', 'Bumiputera Lain (Sabah)', 
		'Bumiputera Sabah Lain', 'Bumiputera 
		Sarawak Lain', 'Che Wong', 'Dumpas', 'Dusun', 'Iban', 
		'Iban / Dayak Laut', 'Idahan', 'Iranum', 'Jahai', 'Kadayan', 'Kadazan', 
		'Kajang', 'Kalabit', 'Kanowit', 'Kejaman', 'Kelabit', 'Kenyah', 
		'Keturunan Pulau Kokos', 'Kintak', 'Kwijau', 'Lahanan', 'Lanoh', 'Lisum', 
		'Lotud', 'Lugat' 'Lun Bawang / Murut (Sarawak)', 'Lundayuh / Lundayeh', 
		'Mahmeri', 'Mangkaak', 'Melanau' 'Mendriq' 'Murut', 'Murut (Sabah)' 'Orang 
		Kanaq', 'Orang Kuala', 'Orang Seletar', 'Orang Sungai / Sungoi', 
		'Orang Sungei', 'Paitan', 'Penan', 'Punan', 'Rumanau', 'Rungus', 'Sabup', 
		'Sekapan', 'Semai', 'Semai (Senoi)', 'Semalai', 'Semoq Beri', 'Senoi Lain', 
		'Sino-native', 'Sulu', 'Suluk', 'Tidung', 'Ukit', 'Temiar', 'Temiar (Senoi)', 
		'Temuan') THEN
			NEW_ETHNICITY='Bumiputera 
lain ^S={font_style=italic} / Other Bumiputera';
	else IF CITIZENSHIP IN ('Valid Blank (Warganegara Malaysia sahaja)' , 
		'Valid blank') and ETHNICITY in ('Melayu', 'Melayu Asli' 'Melayu Brunei', 
		'Melayu Proto Lain', 'Semelai (Melayu Proto)', 'Temuan (Melayu Proto)') then
			NEW_ETHNICITY='Melayu ^S={font_style=italic} / Malays';
Else
		do NEW_ETHNICITY='NOUSE';
		end;

	if NEW_ETHNICITY IN ('NOUSE') THEN
			NEW_CITIZENSHIP='Bukan Warganegara Malaysia ^S={font_style=italic}/*Non-   Malaysian citizens *'; ELSE NEW_CITIZENSHIP= 'UNUSE';
run;

I have also changed the missing values on the NEW_ETHNICITY and NEW_CITEZENSHIP variables to 'NOUSE' and 'UNUSE' respectively. In order to avoid the confusion with the missing values. and here is the proc tabulate codes. 

TITLE j=left "Jadual B1 : Jumlah penduduk mengikut kumpulan etnik, kawasan pihak berkuasa tempa'tan dan negeri, Malaysia, 2010";
Title2 font=bold italic j=left "Table   B1 : Total population by ethnic group, local authority area and state, Malaysia, 2010";
title3 " ";
title4 j=left "Negeri : NEGERI SEMBILAN";
title5 font=bold italic j=left "State";
ods escapechar='^';

proc tabulate data=WORK.DOSM order=data;

	class DISTRICT NEW_ETHNICITY NEW_CITIZENSHIP / missing;
	classlev NEW_ETHNICITY;
	CLASSLEV NEW_CITIZENSHIP;
	classlev district;
	var ID;
	table ALL DISTRICT=' ', ALL 
		NEW_ETHNICITY='Warganegara Malaysia ^S={font_style=italic}
	
	/ Malaysian Citizens'  NEW_CITIZENSHIP='Bukan Warganegara Malaysia ^S={font_style=italic}/*Non-   Malaysian citizens *' 
	/Box='Daerah Pentadbiran/Kawasan Pihak Berkuasa Tempatan ^S={font_style=italic}
														Administrative District/Local Authority Area';
	
	keylabel N=' ';
	keylabel all='Jumlah ^S={font_style=italic}/ Total';
	footnote font=arial bold j=left "Nota" font=arial bold italic "/Note:";
	footnote2 j=l f='ARIAL amt/bold' "^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^Angka-angka di atas tidak disesuaikan untuk kurang penghitungan.";
	footnote3 j=l font=bold italic "^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^_^The above figures have not been adjusted for under enumeration.";
run;

The output for this proc tabulate is as per below. 

I tried to use where statement such as the following: 

where NEW_ETHNICITY IN ('Lain-Lain / Others', 'India /  Indians', 'Cina / Chinese', 
		'Bumiputera 
Lain / Other Bumiputera', 'Melayu / Malays') AND NEW_CITIZENSHIP = 'Bukan Warganegara Malaysia /*Non-   Malaysian citizens *';

However it won't show me any result as the log output says: 

NOTE: No observations were selected from data set WORK.DOSM.

NOTE: There were 0 observations read from the data set WORK.DOSM.

WHERE NEW_ETHNICITY in ('Bumiputera Lain / Other Bumiputera', 'Cina / Chinese', 'India / Indians', 'Lain-Lain / Others',

'Melayu / Malays') and (NEW_CITIZENSHIP='Bukan Warganegara Malaysia /*Non- Malaysian citizens *');

I need the output to only show the following for NEW_ETHNICITY Variable 

-Bumiputera Lain / Other Bumiputera

-Cina / Chinese

-India / Indians

-Lain-Lain / Others'

'Melayu / Malays

And, only the Bukan Warganegara Malaysia /*Non- Malaysian citizens * for the  NEW_CITIZENSHIP variable. 

I do not mind if the UNUSE and NOUSE records to be changed to missing values or anything at all, as long as, the final output on proc tabulate won't display those two records. 

Would you please help me on this. 

Thanks a tonz in advance 

[sheren_deep1]

Hi, 

I tried that but still it won't show me any result 😞 

[PGStats]

I have the feeling that all your observations are excluded (when the MISSING option is absent) because there is always one of the class variables missing (not always the same variable) for every observation in your dataset.