CODEWITHSUNDEEP
sasproc
Sherendeep Kaur
Sherendeep Kaur

Reputation: 53

SAS - omit missing values on proc tabulate procedure

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 do not specify /missing on the class variable, the result won't appear and the log shows a warning that says:

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

Below is the proc tabulate step of the dataset: 

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  MELAYU BUMI_LAIN CINA INDIA LAIN Bumiputera /missing;

    var ID;
    table ALL DISTRICT=' ', ALL 
        NEW_ETHNICITY={LABEL=' '}* (( Bumiputera ={LABEL=' '}*  (MELAYU={LABEL=' '}* N 
BUMI_LAIN={LABEL=' '} * N ALL) INDIA={LABEL=' '}*N  CINA={LABEL=' '}*N LAIN={LABEL=' '} *N ))



    NEW_CITIZENSHIP={LABEL=' '} 
    /Box='Daerah Pentadbiran/Kawasan Pihak Berkuasa Tempatan ^S={font_style=italic}
                                                        Administrative District/Local Authority Area' row=float;

    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 below is my output:

Output

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

Upvotes: 1

Views: 793

Answers (1)

Reeza
Reeza

Reputation: 21274

You can have multiple CLASS statements. Identify which variable is missing ( I think New_Ethnicity) and move that to a new CLASS statement without the missing option on that variable.

Example based on linked duplicate above.

Multiple CLASS statements appear to work in this case: 

data test_cars;
  set sashelp.cars;
  if _n_=5 then call missing(make);
  if _n_=7 then call missing(model);
  if _n_=10 then call missing(type);
  if _n_=13 then call missing(origin);
run;

proc tabulate data=test_cars out=test_tabulate(rename=n=count);
class make model type /missing;
class origin;
tables (make model type),origin*n;
run;

Thanks @Joe for making sample data ;)

Upvotes: 0

Related Questions