BookmarkSubscribeRSS Feed
yash82
Calcite | Level 5

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

8 REPLIES 8
Cynthia_sas
SAS Super FREQ

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;


only_sum_col3_col5.jpg
yash82
Calcite | Level 5

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

Ksharp
Super User

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

Like:

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

line str $400.;

Ksharp

yash82
Calcite | Level 5

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;

Cynthia_sas
SAS Super FREQ

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

yash82
Calcite | Level 5

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

Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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


total_col3_col4.jpg

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1110 views
  • 3 likes
  • 3 in conversation