topic Re: Proc report inserting last row problem in SAS Procedures

Proc report inserting last row problem

Woodyfc — Wed, 08 Jan 2020 17:44:34 GMT

Hello everyone, I encounter some trouble when using proc report.

my source data's last row is manually calculated and modified and then use concatenate to "merge" them, but some variables are intended to be empty, like this:

Var: Code name number value_a value_b rate

: : : : : : :

last2: 999 apple 10 7 3 0.3

last: (blank) Total 1000 900 100 0.1

the problem is that when i use "defline code / group" my last row in data can not display in the proc report output and just disappeared, I would like to ask is there any other good ways to insert a row in proc report?

Thank you all very much !!!!

Re: Proc report inserting last row problem

Cynthia_sas — Wed, 08 Jan 2020 21:16:00 GMT

Hi:

Without some sample code and your data and information about your ODS destination, it is nearly impossible to make any suggestions.

What code have you tried? What is your destination of interest? Can you post some test data that illustrates the issue?

Cynthia

Re: Proc report inserting last row problem

Woodyfc — Thu, 09 Jan 2020 01:59:36 GMT

Hello Cynthia, thank you very much for helping me and here is my sample data and my code. I first use proc summary to extract the column and calculate their total (b).

Then I used them to calculate the new variable (c), and I used concatenating method to combine them (d). After all, I used proc report

to make the output. But it turns out does not include my last raw (my handmade column sum and total rates, (c)). Since the total rates in last row are not the column sum, so I can not simply use summarize to make this problem. Thank you !!!!

data a;
input code type $ name $ total good bad good_rate bad_rate;
datalines;
11 a apple 10 8 2 0.8 0.2
11 b apple 10 7 3 0.7 0.3
11 c apple 20 10 10 0.5 0.5
13 a banana 10 8 1 0.8 0.2
17 a orange 30 27 3 0.9 0.1
17 b orange 30 18 12 0.6 0.4
;

proc summary data = a;
var total good bad;
output out = b (drop = _type_ _freq_);
sum = total good bad;

run;

data c;
set b;
name = 'Total'
good_rate = round((good/total), .1);
bad_rate = round((bad/total), .1);
run;

data d;
set a c;
run;

proc report data = d;
column _all_;
define code / group;
define type / display;
define name / display;
define total / display;
define good / display;
define bad / display;
define good_rate / display;
define bad_rate / display;
run;

Re: Proc report inserting last row problem

Woodyfc — Thu, 09 Jan 2020 02:25:58 GMT

I would like to output as a pdf file. Thank you

Re: Proc report inserting last row problem

Woodyfc — Sun, 12 Jan 2020 10:36:35 GMT

I found out that the missing value in a numeric variable column cannot be grouped or ordered, otherwise the row containing the missing value (.) would be disappeared.