I have a clean data which has no duplicates record. I managed to get my expected result by using PROC PRINT. The reason why i want to use LIST REPOrT is that it is a simpler way to do reporting by using PROC REPORT without hard code it which im not familiar with. Moreoever, in proc print, i cant add a word "SUM" or "TOTAL" at the SUM row. Ppl here are suggesting me to use PROC REPORT but it is not as easy as what u might think considering that im not an expert in SAS EG that much. as you all can see, by using LIST REPORT/"PROC REPORT", i can get a "Total" label at the last row which cannot be done by PROC PRINT. In case u want to see the code generated by the system: TITLE1 "List Report";
FOOTNOTE1 "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
proc report data=WORK.JOIN2BCAE nowd;
column X_BCAE_NAME X_BCAE_POSITION X_BCAE_MORTGAGE, SUM=X_BCAE_MORTGAGE_SUM X_BCAE_CASHFLOW cv1 X_BCAE_CARLOAN cv2 X_BCAE_INCOME cv3 newsum cv4;
define X_BCAE_NAME / group 'X_BCAE_NAME' format=$4000. missing order=formatted;
compute X_BCAE_NAME;
if _break_ eq ' ' then do;
if X_BCAE_NAME ne ' ' then hold1=X_BCAE_NAME;
end;
if upcase(_break_)="X_BCAE_NAME" then do;
call define("X_BCAE_NAME", 'style', 'style=[pretext="Subtotal "]');
end;
if _break_='_RBREAK_' then do;
call define("X_BCAE_NAME", 'style', 'style=[pretext="Total"]');
end;
endcomp;
define X_BCAE_POSITION / group 'X_BCAE_POSITION' format=$4000. missing order=formatted;
compute X_BCAE_POSITION;
if X_BCAE_POSITION ne ' ' then hold2=X_BCAE_POSITION;
if X_BCAE_POSITION eq ' ' and _break_ eq ' ' then X_BCAE_POSITION=hold2;
endcomp;
define X_BCAE_MORTGAGE / analysis SUM 'X_BCAE_MORTGAGE' missing;
define X_BCAE_CASHFLOW / group missing noprint;
define cv1 / computed 'X_BCAE_CASHFLOW' missing;
compute cv1 / char;
if _break_ eq ' ' then do;
if X_BCAE_CASHFLOW ne . then hold3=X_BCAE_CASHFLOW;
cv1=put(hold3,8.);
end;
if _break_ ne ' ' then cv1=' ';
if upcase(_break_)="X_BCAE_NAME" then cv1=put(hold3,8.);
endcomp;
define X_BCAE_CARLOAN / group missing noprint;
define cv2 / computed 'X_BCAE_CARLOAN' missing;
compute cv2 / char;
if _break_ eq ' ' then do;
if X_BCAE_CARLOAN ne . then hold4=X_BCAE_CARLOAN;
cv2=put(hold4,8.);
end;
if _break_ ne ' ' then cv2=' ';
if upcase(_break_)="X_BCAE_NAME" then cv2=put(hold4,8.);
endcomp;
define X_BCAE_INCOME / group missing noprint;
define cv3 / computed 'X_BCAE_INCOME' missing;
compute cv3 / char;
if _break_ eq ' ' then do;
if X_BCAE_INCOME ne . then hold5=X_BCAE_INCOME;
cv3=put(hold5,8.);
end;
if _break_ ne ' ' then cv3=' ';
if upcase(_break_)="X_BCAE_NAME" then cv3=put(hold5,8.);
endcomp;
define newsum / group missing noprint;
define cv4 / computed 'newsum' missing;
compute cv4 / char;
if _break_ eq ' ' then do;
if newsum ne . then hold6=newsum;
cv4=put(hold6,8.);
end;
if _break_ ne ' ' then cv4=' ';
if upcase(_break_)="X_BCAE_NAME" then cv4=put(hold6,8.);
endcomp;
break after X_BCAE_POSITION / summarize;
rbreak after / summarize;
run;
quit;
TITLE; FOOTNOTE; Your advice is highly appreciated
... View more