proc report - how to add a calculated filed

[tparvaiz]

Hi,

I am using proc report to create a report with few columns... Report is grouped by year_month.

Want to add one more row @ the end of each year_month (grouped by variable) to show calculated filed... calculated filed (let's call it field_A) will be based on the formula as follows

Field_A = 1000/VOL_PER_HR

may I know how to accomplish it... Here is the sample code that I've used

proc report data=WORK.Table_A nowd;

  column YEAR_MONTH_DT cv1 VOL_PER_HR, SUM=VOL_PER_HR_SUM;

  define YEAR_MONTH_DT / group format=YYMMDD10. missing noprint order=internal;

  define cv1 / computed 'YEAR_MONTH_DT' format=YYMMDD10. missing;

  compute cv1;

  if YEAR_MONTH_DT ne . then hold1=YEAR_MONTH_DT;

  cv1=hold1;

  endcomp;

  define VOL_PER_HR / analysis SUM 'VOL_PER_HR' format=COMMA10. missing;

  define VOL_PER_HR_SUM / format=COMMA10.;

  run;

quit;

[tparvaiz]

anyone...?

[Cynthia_sas]

Hi:

  There's not enough information to give you much help. You do not show your data. You showed a program that doesn't make a lot of sense without an idea of what the data looks like.  For example -- how many observations do you have for every unique value of YEAR_MONTH_DT????

  Without seeing your data or a sample of your data, it is hard to understand why you are crossing VOL_PER_HOUR with the SUM statistic and why you give the SUM statistic an alias of VOL_PER_HR_SUM (because you don't have to give a name to a nested SUM).

  Your creation of CV1 is unnecessary, since you have YEAR_MONTH_DT as the only GROUP item, you will get 1 report row for every unique value for YEAR_MONTH_DT. So, creating CV1 is unnecessary because this technique is the kind of technique you use when you might have blanks in the rows for the group variables (which only happens if you have more than 1 GROUP item in the column).

  And, finally, you say that you "Want to add one more row @ the end of each year_month" -- but your data are not grouped to the YEAR_MONTH level -- your format is YYMMDD10. -- so you are NOT grouping to YEAR_MONTH -- but to the day level. So PROC REPORT will be unable to do break processing for the YEAR_MONTH level based on what you show.

  PROC REPORT can only break on the GROUP or ORDER items that you use on the report. I do not see a BREAK statement in your code. Without a BREAK statement, there can be no "extra" row insertion for each YEAR_MONTH unique value.

  Consider the following program. It uses a few groups from SASHELP.CLASS (ages 12, 13 and 14). The variables AGE and SEX are both group variables. There are 2 calculated variables on the report; CALC1 and CALC2. HEIGHT is the numeric variable on the report. The default statistic for HEIGHT is the SUM statistic.

  The N statistic (the COUNT) has been requested on the report so you can see that more than 1 observation contributes to each row.

  DISP_AGE is computed for each report row, because otherwise, AGE would only appear on the first row for each group and would be blank on the other rows. I did not bother to use NOPRINT on any items because I wanted to show you the difference between AGE and DISP_AGE. There would be no point in creating a similar value for the SEX variable because each AGE only has a single row for each unique value of the SEX variable.

  CALC1 and CALC2 are silly calculations. CALC1 is HEIGHT*10 and CALC2 is 100 divided by HEIGHT. Note however, that HEIGHT is automatically summarized because the usage is SUM. I did not need to use HEIGHT,SUM in the COLUMN statement because I automatically will get the SUM for HEIGHT by having a DEFINE statement with SUM as the usage for HEIGHT. Since HEIGHT is defined as an ANALYSIS usage with the SUM statistic, I have to use the compound name HEIGHT.SUM in the assignment statements for CALC1 and CALC2.

  Note how AGE is used in the BREAK AFTER statement. The only way to "insert" a report row with any kind of summary information is to use either a BREAK statement with the SUMMARIZE option. Or, you could use a LINE statement, but you might want to work with the program and output below to see whether any of these techniques are transferable to your question.

cynthia

ods html file='c:\temp\showage.html';

  proc report data=sashelp.class nowd split='#';

    title 'PROC REPORT Calculated Vars';

    where age in (12, 13, 14);

    column age disp_age sex n height calc1 calc2;

    define age / group;

    define disp_age / computed;

    define sex / group;

    define n / 'Count of Obs';

    define height / sum;

    define calc1 / computed 'calc1#height*10';

    define calc2 / computed 'calc2#100/height';

    compute disp_age;

       if age ne . then hold1 = age;

       disp_age = hold1;

    endcomp;

    compute calc1;

      calc1 = height.sum *10;

    endcomp;

    compute calc2;

      calc2 = 100 / height.sum;

    endcomp;

    break after age / summarize;

    compute after age;

      line ' ';

    endcomp;

run;

ods html close;

title;