topic Re: How to deduct 1 from observations in PROC REPORT in SAS Enterprise Guide

How to deduct 1 from observations in PROC REPORT

imdickson — Thu, 17 Mar 2016 07:40:25 GMT

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;

Re: How to deduct 1 from observations in PROC REPORT

Kurt_Bremser — Thu, 17 Mar 2016 08:13:14 GMT

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.

Re: How to deduct 1 from observations in PROC REPORT

imdickson — Thu, 17 Mar 2016 08:16:25 GMT

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?

Re: How to deduct 1 from observations in PROC REPORT

Kurt_Bremser — Thu, 17 Mar 2016 08:18:10 GMT

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

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

Re: How to deduct 1 from observations in PROC REPORT

imdickson — Thu, 17 Mar 2016 08:22:57 GMT

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....

Re: How to deduct 1 from observations in PROC REPORT

Kurt_Bremser — Thu, 17 Mar 2016 08:31:18 GMT

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;

Re: How to deduct 1 from observations in PROC REPORT

imdickson — Thu, 17 Mar 2016 08:56:21 GMT

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

Re: How to deduct 1 from observations in PROC REPORT

Kurt_Bremser — Thu, 17 Mar 2016 09:20:01 GMT

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.

Re: How to deduct 1 from observations in PROC REPORT

imdickson — Thu, 17 Mar 2016 09:25:18 GMT

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?

Re: How to deduct 1 from observations in PROC REPORT

Ksharp — Thu, 17 Mar 2016 09:31:03 GMT

Not tested. This could worked ?


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

Re: How to deduct 1 from observations in PROC REPORT

Kurt_Bremser — Thu, 17 Mar 2016 09:45:16 GMT

@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.

Re: How to deduct 1 from observations in PROC REPORT

imdickson — Thu, 17 Mar 2016 09:57:53 GMT

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

Re: How to deduct 1 from observations in PROC REPORT

Kurt_Bremser — Thu, 17 Mar 2016 09:59:24 GMT

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

Where I stated in my previous post.

Re: How to deduct 1 from observations in PROC REPORT

Ksharp — Fri, 18 Mar 2016 00:55:45 GMT

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