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
... View more