11-14-2011 03:02 PM
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
11-14-2011 08:06 PM
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.
length col1 $15;
input col1 $ col2 col3 col4 col5;
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
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;
col1 = 'Research';
ods _all_ close;
11-15-2011 04:47 AM
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'
('Absolute values after shock'
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;
if EXT_REFR='Diversification' then do;
11-15-2011 09:36 AM
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);
if _break_ = '_RBREAK_' then do;
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:
if EXT_REFR = 'Diversification' then do;
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.
11-15-2011 02:54 PM
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
11-15-2011 10:55 PM
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.
11-16-2011 10:22 AM
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.