LIST REPORT duplicates all my data

imdickson — Wed, 16 Mar 2016 06:11:21 GMT

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

Re: LIST REPORT duplicates all my data

Kurt_Bremser — Wed, 16 Mar 2016 08:44:33 GMT

define X_BCAE_POSITION

change "group" to "display".

"group" causes a break after each entry and creates a summary line.

Edit:

As far as I see, you don't need any group variable at all, so you should have only "display" or "analysis" in your DEFINE statements.

Re: LIST REPORT duplicates all my data

imdickson — Wed, 16 Mar 2016 09:23:56 GMT

Hey there, i tried changing to display but it is still giving me the same result.

2nd update: i tried to use the most basic PROC REPORT hardcode way and i found out if i use BREAK AFTER, i will get the same duplicate records but if i use RBREAK, no more duplications....can you tell me their differences and my question is here, how do i add a label "SUM" or "TOTAL" at the last row under "POSITION" variable?

Re: LIST REPORT duplicates all my data

imdickson — Wed, 16 Mar 2016 09:56:54 GMT

I added this code before RBREAK:

compute


 X_BCAE_POSITION; 
if


 _break_ ne ' ' then call define('age','style','style=[pretext="total"]'); 
endcomp


;

Its not working

Re: LIST REPORT duplicates all my data

Kurt_Bremser — Wed, 16 Mar 2016 10:52:28 GMT

Could you please post some test data in a data step with CARDS?

topic Re: LIST REPORT duplicates all my data in SAS Enterprise Guide

LIST REPORT duplicates all my data

Re: LIST REPORT duplicates all my data

Re: LIST REPORT duplicates all my data

Re: LIST REPORT duplicates all my data

Re: LIST REPORT duplicates all my data