topic Re: Proc report breaking after column help needed in ODS and Base Reporting

Proc report breaking after column help needed

yash82 — Mon, 14 Nov 2011 20:02:26 GMT

Hi Guys, I have to prepare report like this....

col1 col2 col3 col4 col5
name1 20 30 40 50
name2 60 70 80 90
.
.
.
Research 40 100

This research variable is at line 10 and should only contain values for col3 and col5... I am using currently line @ and put funtions in compute after... but its not providing good display... what approach should I apply here ??? Any hints would be really appreciable

Re: Proc report breaking after column help needed

Cynthia_sas — Tue, 15 Nov 2011 01:06:10 GMT

Hi:

You didn't show any code. Are you using an RBREAK AFTER statement? When you use PROC REPORT's automatic break processing statements, it will only summarize the variables and report items that have been defined (DEFINE statement) with an analytic usage, such as SUM. If you DEFINE the variables (such as COL2 and COL4) with a usage of DISPLAY, they will NOT be summarized when PROC REPORT does break processing. For example, the code below produced the attached screen shot.

cynthia

data names;

length col1 $15;

infile datalines;

input col1 $ col2 col3 col4 col5;

return;

datalines;

alan 20 30 40 50

barb 60 70 80 90

carl 30 40 50 60

dana 50 60 70 80

eddie 10 20 30 40

fran 10 10 10 10

george 20 20 20 20

;

run;

ods listing close;

ods html file='c:\temp\sum_col3_col5.html' style=sasweb;

proc report data=names nowd;

title 'Proc REPORT -- only usage of SUM will summarize on grand total line';

title2 'Usage of DISPLAY (COL2, COL4) will not summarize';

column col1 col2 col3 col4 col5;

define col1 / order ;

define col2 / display;

define col3 / sum;

define col4 / display;

define col5 / sum;

rbreak after /summarize;

compute after;

col1 = 'Research';

endcomp;

run;

ods _all_ close;

title;

Re: Proc report breaking after column help needed

Ksharp — Tue, 15 Nov 2011 03:17:35 GMT

You can make a variable to hold this string.Adjust blanks as your destination.

Like:

str='Resut: '||strip(col3.sum)||' ';

line str $400.;

Ksharp

Proc report breaking after column help needed

yash82 — Tue, 15 Nov 2011 07:35:44 GMT

Thank you cynthia.... This gave me a good guideline to solve my purpose....

Re: Proc report breaking after column help needed

yash82 — Tue, 15 Nov 2011 09:47:47 GMT

Thanks a lot guys, I Have one more question regarding conditional statements ...... My condition is if EXTR_REFR='Diversification' then only input column 2 and column 4 .... Now I am confused whether I should use it in Data Step or should I compute it in Proc report. Please look into my compute after block and help me out . As in my final row it is showing only nulls

proc report data=final_temp nowd ;
column ('Initial absolute values before shock'
SCC_AF_SHK_INCLDG_LAC_TPR_TUR=t1
SCC_AF_SHK_EXCLDG_LAC_TPR_TUR=t2
EXT_REFR
ABS_VAL_OF_AST_BFR_SHK
ABS_VAL_OF_LBY_BFR_SHK )
('Absolute values after shock'
ABS_VAL_OF_AST_AF_SHK
ABSVAL_LBY_AF_SHK_INCDG_LAC_TPR
SCC_AF_SHK_RSK_INCDG_LAC_TPR
ABSVAL_LBY_AF_SHK_EXCDG_LAC_TPR
SCC_AF_SHK_RSK_EXCDG_LAC_TPR
);
define t1 / format=commax14.2 noprint;
define t2 / format=commax14.2 noprint;
define EXT_REFR / 'Life underwriting risk - Basic information';
define ABS_VAL_OF_AST_BFR_SHK / display    format=commax14.2 'Assets';
define ABS_VAL_OF_LBY_BFR_SHK / display format=commax14.2 'Liabilities';
define ABS_VAL_OF_AST_AF_SHK /   display format=commax14.2 'Assets';
define ABSVAL_LBY_AF_SHK_INCDG_LAC_TPR / display format=commax14.2 'Liabilities (including the loss absorbing capacity of technical provisions)';
define SCC_AF_SHK_RSK_INCDG_LAC_TPR / sum format=commax14.2 'Net solvency capital requirement (including the loss-absorbing capacity of technical provisions)';
define ABSVAL_LBY_AF_SHK_EXCDG_LAC_TPR / display     format=commax14.2 'Liabilities ';
define SCC_AF_SHK_RSK_EXCDG_LAC_TPR / sum format=commax14.2 'Gross solvency capital requirement';
rbreak after /summarize;
compute after;
if EXT_REFR='Diversification' then do;
_col2_=t1;
_col4_=t2;

endcomp;
run;

Proc report breaking after column help needed

Cynthia_sas — Tue, 15 Nov 2011 14:36:18 GMT

Hi:

I have a few comments...without data, it is hard to make more than just code-based comments:

1) You have a missing DO/END in your COMPUTE block. I would expect that you would get the following ERROR message in the log

ERROR: There was 1 unclosed DO block.

2) By the time a COMPUTE AFTER block is executed, PROC REPORT is at the bottom of the report, but after all the report rows have been written. So I would expect the value of any variable (such as EXT_REFR) to be blank. Without a better understanding of what you are trying to do with this COMPUTE block, it will be hard to help with suggestions. It is possible that the COMPUTE after is the wrong place to make this conditioinal test. In a COMPUTE block for EXT_REFR, you can test for the end of the report (or another condition);

COMPUTE EXT_REFR;

if _break_ = '_RBREAK_' then do;

...more code...

end;

ENDCOMP:

but, if you want to change the value of another variable, such as ABS_VAL_OF_AST_BFR_SHK if there was a certain value for EXT_REFR, then you would need something more like this:

COMPUTE ABS_VAL_OF_AST_BFR_SHK;

if EXT_REFR = 'Diversification' then do;

...more code...

end;

ENDCOMP:

3) You say that you want to "input" col2 and col4. PROC REPORT does not have the concept of reading in variables like an INFILE statement, but since you used an assignment statement, it seems to me that you want to assign the value of t1 to what???? _COL2_ and _COL4_ are NOT on your COLUMN statement are they temporary variables.? If you were trying to mimic PROC REPORT's syntax, you have the wrong absolute columne names. PROC REPORT does have the concept of absolute column names, but only with items under an ACROSS variable, which you do not have. In this case, it would not be appropriate to use absolute column names.

So, it would be better to refer to your columns by their name in the column statement rather than calling them _COL2_ and _COL4_. Which columns do you mean as #2 and #4??? ABS_VAL_OF_AST_BFR_SHK and ABS_VAL_OF_AST_AF_SHK?

4) I don't actually understand why you create T1 and T2 -- Maybe, you only want to show the summary values for SCC_AF_SHK_INCLDG_LAC_TPR_TUR and SCC_AF_SHK_EXCLDG_LAC_TPR_TUR on the summary line and NOT on every row????

I wold expect that the code you have is not giving you the desired results. It might be better for you to work with Tech Support on this report. They can look at all your data and all your code and help you achieve your desired results.

cynthia

Re: Proc report breaking after column help needed

yash82 — Tue, 15 Nov 2011 19:54:00 GMT

Hi Cynthia,Thanks a lot for digging deep in this problem... Let me provide a sample data so that we can have clear vision on what I am set out to achieve.

Life underwriting risk col1 col2 col3 col4 col5 col6 col7

Derivative 100 200 300 400 500 600 700

Morality 200 400 500 600 700 800 900

Simplification 400 600

Risk Category 400 600 700 900 1300 500 600

Manipulations 300 200

Total category 2600 3400

See here, if the value for Life Undersriting risk is Simplification and Manipulation then I have to output only col3 and col4 and remaining fields should be blank and finally I need a summary on col3 and col4. I can get summary on these 2 fields by grouping but how should I conditionally display it and that too at the same position is something I am worried about. Hope I have make my requirement clear..... Thanks a lot again for your efforts

Re: Proc report breaking after column help needed

Ksharp — Wed, 16 Nov 2011 03:55:48 GMT

You can use out= option to adjust your code.

I think your compute after is not right.

ext_refr is always null for your situation.

You'd better to post some sample data and final report you want to see.

I also notice that

Total category 2600 3400

does not equal the total of col3 col4.

Ksharp

Re: Proc report breaking after column help needed

Cynthia_sas — Wed, 16 Nov 2011 15:22:17 GMT

Hi:

Are the requirements really firmed up yet? Initially you said you wanted to sum only COL3 and COL5 variables, now you only need to sum and show COL3 and COL4 on selected rows. I am still not certain what the data looks like, since your example seems like the final report and not the initial data. This modification to the program I posted above "blanks out" various columns based on the value of the EXT_REFR variable. Do note that I have called my numeric variables COL1-COL7....and refer to them as such in my COMPUTE block. If your numeric variables were named FRED, ETHEL, LUCY, RICKY, KERMIT, ELMO and OSCAR, then you would use those variable names in your COLUMN statement and in your COMPUTE block. (Of course, if those were the variable names, it would be a truly interesting report.) See the attached screenshot for a possible approach. Since the COL1-COL7 variables are numeric, they must be set to missing (.) and then using the MISSING option allows the . to display as a blank in the final report.

cynthia