Desktop productivity for business analysts and programmers

How to deduct 1 from observations in PROC REPORT

Reply
Contributor
Posts: 67

How to deduct 1 from observations in PROC REPORT

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?

PROC PRINT OBS.jpg

 

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;

 

 

 

Esteemed Advisor
Posts: 6,693

Re: How to deduct 1 from observations in PROC REPORT

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 67

Re: How to deduct 1 from observations in PROC REPORT

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?

Esteemed Advisor
Posts: 6,693

Re: How to deduct 1 from observations in PROC REPORT

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

data xxx;
set xxx;
obs = _N_;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 67

Re: How to deduct 1 from observations in PROC REPORT

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....
Esteemed Advisor
Posts: 6,693

Re: How to deduct 1 from observations in PROC REPORT

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 67

Re: How to deduct 1 from observations in PROC REPORT

i think this works like ur sample code:
compute obs;
if _break_ ne '_RBREAK_' then dsobs +1;
obs=dsobs;
endcomp;
Esteemed Advisor
Posts: 6,693

Re: How to deduct 1 from observations in PROC REPORT

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 67

Re: How to deduct 1 from observations in PROC REPORT

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?
Esteemed Advisor
Posts: 6,693

Re: How to deduct 1 from observations in PROC REPORT


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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 9,584

Re: How to deduct 1 from observations in PROC REPORT

[ Edited ]

Not tested. This could worked ?

 


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

Re: How to deduct 1 from observations in PROC REPORT

where exactly do i put this options missing=' '; ?
Esteemed Advisor
Posts: 6,693

Re: How to deduct 1 from observations in PROC REPORT


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

Where I stated in my previous post.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 9,584

Re: How to deduct 1 from observations in PROC REPORT

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

Ask a Question
Discussion stats
  • 13 replies
  • 483 views
  • 0 likes
  • 3 in conversation