How to deduct 1 from observations in PROC REPORT

[imdickson]

Hello, Im using SAS EG to do PROC REPORT.

I have a problem here. I have managed to add Obs into PROC REPORT however, the Obs appears until the very last row of the table which is already at the SUM/TOTAL row. I want my obs up to the last row before SUM. Is there a way to do so?

My code:

proc report data=WORK.JOIN2BCAE;
COL obs X_BCAE_NAME X_BCAE_POSITION X_BCAE_INCOME X_BCAE_CARLOAN X_BCAE_MORTGAGE X_BCAE_CASHFLOW /*newsum*/ newcompute;

define obs / computed "No.";
define X_BCAE_NAME / display "Name";
define X_BCAE_POSITION / display "Position";
define X_BCAE_INCOME / sum "Income";
define X_BCAE_CARLOAN / order sum "Car Loan";
define X_BCAE_MORTGAGE / analysis "Mortgage";
define X_BCAE_CASHFLOW / analysis "Cash Flow";
define newcompute / computed "Maximum New Loan";
compute newcompute;
newcompute=X_BCAE_INCOME.sum-X_BCAE_CARLOAN.sum-X_BCAE_MORTGAGE.sum-1500;
ENDCOMP;
compute obs;
dsobs +1;
obs=dsobs;
endcomp;
/*compute CARLOAN;*/
/*CARLOAN=CARLOAN.SUM;*/
/*ENDCOMP;*/


/*compute X_BCAE_POSITION; */
/*if _break_ ne ' ' then call define('age','style','style=[pretext="total"]'); */
/*endcomp; */

compute after;
       X_BCAE_POSITION='TOTALS:';
    endcomp;
	rbreak after  /skip summarize dol dul;

RUN;

[Kurt_Bremser]

Add the observation number to the dataset in a previous step, and use it with type "display" in proc report. This keeps it from appearing in the summary line.

[imdickson]

Hi Kurt, u mean before compute, do 1 datastep to store the number of records? for example 1 to 9 if my records has 9 rows?

[Kurt_Bremser]

Yes. Make sure that the dataset already has the correct order, and then do

data xxx;
set xxx;
obs = _N_;
run;

[imdickson]

just to ask again, this datastep is outside PROC REPORT or within PROC REPORT? because if i put in PROC REPORT, all my compute shows red color and error....

[Kurt_Bremser]

The data step would have to happen separately before proc report.

But I found a better way:

compute obs;
dsobs + 1;
if _break_ ne ' ' then call define('obs','style','style=[pretext=""]'); else obs = put(dsobs,best.);
endcomp;

[imdickson]

i think this works like ur sample code:
compute obs;
if _break_ ne '_RBREAK_' then dsobs +1;
obs=dsobs;
endcomp;

[Kurt_Bremser]

In my test

proc report data=sashelp.class;
col obs name weight;
define obs / computed "No.";
define name / display 'Name';
define weight / analysis 'Weight';
compute obs;
if _break_ ne '_RBREAK_' then dsobs +1;
obs=dsobs;
endcomp;
rbreak after /summarize;
run;

this only prevented the increment, but still displayed the number of the previous step

This worked better:

compute obs;
if _break_ ne '_RBREAK_' then do;
  dsobs +1;
  obs=dsobs;
end;
endcomp;

I think this is the most elegant (bc. simple) solution.

[imdickson]

Hi Kurt, however, in the results, there is a .(which indicates a null) in the TOTAL/SUM row. Any idea on how to eliminate that?

[Kurt_Bremser]

---

@imdickson wrote:
Hi Kurt, however, in the results, there is a .(which indicates a null) in the TOTAL/SUM row. Any idea on how to eliminate that?

---

Ah, I had options missing = ' '; active.

Add

options missing = ' ';

before the proc report.

[Ksharp]

Not tested. This could worked ?

options missing=' ';
................
compute after;
       X_BCAE_POSITION='TOTALS:';
obs=. ;
endcomp;

[imdickson]

where exactly do i put this options missing=' '; ?

[Kurt_Bremser]

---

@imdickson wrote:
where exactly do i put this options missing=' '; ?

---

Where I stated in my previous post.

[Ksharp]

At the start of your code. It is system option.