turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- How to deduct 1 from observations in PROC REPORT

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 03:40 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 04:13 AM

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

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 04:16 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 04:18 AM

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

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 04:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 04:31 AM

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

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 04:56 AM

i think this works like ur sample code:

compute obs;

if _break_ ne '_RBREAK_' then dsobs +1;

obs=dsobs;

endcomp;

compute obs;

if _break_ ne '_RBREAK_' then dsobs +1;

obs=dsobs;

endcomp;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 05:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 05:25 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 05:45 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 05:29 AM - edited 03-17-2016 05:31 AM

Not tested. This could worked ?

options missing=' ';

................

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 05:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 05:59 AM

imdickson wrote:

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

Where I stated in my previous post.

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-17-2016 08:55 PM

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