Hi, Is there a way to ensure that missing entries for character fields have blanks and missing entries for numeric fields have the decimal point when importing data and when working datsets. Thanks.   data ListFinal; set ListFinal; array NumVar _numeric_; do over NumVar; if NumVar < 0 and NumVar not in (.) then NumVar=.; end; run; data ListFinal; set ListFinal; array CharVar _character_; do over CharVar; if CharVar in ('.',''," ",' ') then CharVar=''; end; run; ... View more

Hi...I am trying to include also two more row totals in the same summary table and the table below is what I would like to end up with. The two row totals I would like to add is under the second column, PSec - Total and under the first column, Grand Total. I have included the code that I have so far. Any suggestions....Thanks.     AY   2013/14 2014/15 2015/16 2016/17 2017/18 2018/19 2019/20 Sec MHS                .              . 15 33 25 15              .   Sec Sec   228 220 351 370 401 392 6     Sec - Total   228 220 366 403 426 407 6   PSec ELT EAP              .              . 96 441 623 485 89   PSec ELT ESL 1385 1460 1523 1537 521 551 15     ELT - Total   1385 1460 1619 1978 1144 1036 104   PSec YB                . 27 50 38 34 28              .     YB - Total                . 27 50 38 34 28              .   PSec LLI App              . 46 44 49 42 35              .   PSec LLI Other LLI 16 297 247 173 152 119 18     LLI - Total   16 343 291 222 194 154 18     Other PS Stud - Total   1039 1166 1409 1494 1678 1859 947     PSec - Total   2440 2996 3369 3732 3050 3077 1069     ALC - Total                . 1006 1095 1013 989              .          .   Grand Total     2668 4222 4830 5148 4465 3484 1075   PSec Other PS Prog   1047 1177 1419 1610 1816 1966 949     Other PS Prog - Total   1047 1177 1419 1610 1816 1966 949   PSec ELT EAP - Prog              .              . 96 989 1326 1146 89     ELI1 - Total                .              . 96 989 1326 1146 89   PSec ELT ESL - Prog 1385 1460 1523 1537 521 551 15     ELI1 - Total   1385 1460 1523 1537 521 551 15       ods _all_ close; ods listing close; ods noresults; options leftmargin = .5in rightmargin = .5in topmargin =.5in bottommargin =.5in; title; ods excel file="%sysfunc(pathname(project))\Annual Report1 (&rundate).xlsx" options(flow="rowheaders,text,tables"); ods excel options(sheet_name='EnrolmeNT' title_footnote_nobreak="no" embedded_titles='yes' absolute_column_width='20,40,20,10,10,10,10,10,10,10' absolute_row_height='15' tab_color='LightYellow' embedded_titles='yes' suppress_bylines='yes' embedded_Footnotes = 'Yes' orientation='Landscape' sheet_label=' ' sheet_interval ='proc' page_order_across='Yes' pages_fitwidth='1' frozen_headers='on' pages_fitheight = '1' fittopage='yes' row_repeat = '1-2' print_footer='&C CONFIDENTIAL Prepared By: D. Babee &RPage &P OF &N'); proc report data=Have nowd split='00'x; column c1 c2 c3 ('AY'('2013/14'n '2014/15'n '2015/16'n '2016/17'n '2017/18'n '2018/19'n '2019/20'n group1)) ; define c1 / ' ' display; define c2 / ' ' display format=$70.; define c3 / ' ' display; define '2013/14'n / analysis sum order=data; define '2014/15'n / analysis sum order=data; define '2015/16'n / analysis sum order=data; define '2016/17'n / analysis sum order=data; define '2017/18'n / analysis sum order=data; define '2018/19'n / analysis sum order=data; define '2019/20'n / analysis sum order=data; define group1 / order noprint order=data; break after group1 / summarize; compute after group1; c2 = catx(' - ',group1,'Total'); endcomp; quit; ods excel close; ods listing; ... View more