graph report

[sunilreddy]

Hi,

I need your help to write the logic for below requirement

I've two numeric columns(REQ and F) data on monthly basis as attached. 'REQ 'column contains a data for previous(history) months data on monthly basis and Column 'F' contains a data for future months(next 12 months) .

I've attached report structure to print with color format and structure. Could you please help me to write the logic for displaying in attached graph format.

Requirement description:

Please let me know if you require any details

[Reeza]

Why do you have graph in the title? That looks like a table to me.

Have you checked into Cynthia Zender's complex reports paper?

[Cynthia_sas]

Hi:

  Thanks for the recommendation for my paper. To me, this isn't even a complex report. It is pretty straightforward PROC REPORT STYLE= overrides at the statement level. Also, I don't understand why this is a graph question. It looks like straight color coding and removing the interior table lines to me. (Not sure why the gray highlighting extends beyond the boundary of the table, either).

  The attached screen shot didn't use very interesting data, just 3 rows all the same values, but the color coding was pure STYLE= overrides.

cynthia

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

    title 'Sample Report';

    proc report data=gendata nowd split='#'

         style(report)={rules=none frame=void cellspacing=0}

         style(header)={background=verydarkblue color=white

                        font_weight=bold};

      column vdp _2011Y _2012Q1 _2012Q2 

             _2012Q3 _2012Q4 _2012Y _YOY 

             _2013Q1 _2013Q2 ;

      define vdp / order order=data ' ';

      define _2011Y / display '2011Y#REQ'

             style(header)={background=cxeeeeee color=black}

             style(column)={background=cxeeeeee};

      define _2012Q1 /'2012Q1#REQ' display;

      define _2012Q2  /'2012Q2#REQ'   display;

      define _2012Q3 /'2012Q3#REQ+F' display;

      define _2012Q4 / '2012Q4# F ' display;

      define _2012Y /'2012Y#REQ+F'

             style(header)={background=cxeeeeee color=black}

             style(column)={background=cxeeeeee};

      define _YOY /'2011/2012#YoY'

             style(header)={background=cxbbbbbb color=black}

             style(column)={background=cxbbbbbb};

      define _2013Q1 /'2013Q1# F ' display;

      define _2013Q2 /'2013Q2# F' display;

    run;

ods _all_ close;

[sunilreddy]

Hi Cynthia,

Thanks a lot for your response in this.

But i have dynamic column variables. for ex: 2012Y, 2013Q1,2013Q2 etc.

In this case i need same output format with below columns

VDP 2012Y 2013Q1 2013Q2 2013Q3 2013Q4 2013Y. 2014Q1 2014Q2.

So how can i define these dynamic variables in report.

[sunilreddy]

currently i've columns 2011Y  2012Q1 2012Q2 2012Q3 2012Q4,2014Q1 2014Q2 in table. but these columns prefix will change if we move next year. so we need to add new columns 2012Y(sum of current year(2012Q1 to Q4)) and Y0Y(diff of previous year and sum of current year(2012Q1 to Q4))

please suggest me to keep these columns in same output

[Reeza]

I'm assuming you're using EG as you have variable named 2012Y etc?

Why not create macro variables that represent the years and use that instead?

[Cynthia_sas]

Hi:

  I'm not sure what you mean when you say that you have "dynamic" variables. You could mean that you have SAS Macro variables, you could mean that you prompt the user for the variables they want and then you have a program that selects the variable accordingly, you could mean dynamic in that the variables change in every run, you could mean that you are using ACROSS variables in PROC REPORT. I hardcoded some variable names to show that the kind of highlighting that you wanted to do was simple with PROC REPORT and to illustrate that this isn't really a graph question, in my opinion. I don't know what structure your report data is in, whether you have all the columns you need or whether you need to calculate them in a DATA step or a PROC REPORT step. I created a report that showed how to do the highlighting you wanted. Getting your data into the form used by my PROC REPORT is an exercise in mapping the variables you have to the "fake" names I chose in my program.

  I could have called my variables FRED, ETHEL, LUCY, RICKY, WOMBAT, KOALA, WIDGET, GIDGET, WOCKET and WACKET. It really doesn't matter, I called them something similar to what you showed in your screenshot just to make it easier to understand how and where the highlighting was happening.

  Let's assume that I had designed PROC REPORT like this, with "generic" column names:

    column col1 col2 col3 col4 col5 col6 col7 col8 col9 col10;

Then before, my PROC REPORT step, let's say that I take my data and for the purposes of creating a final dataset, I have something like this where I rename my "dynamic" variables to have the names that PROC REPORT is expecting:

data report_final;

   set dyn_data(rename=(vdp=col1 _2011Y=col2 _2012Q1=col3 _2012Q2=col4

             _2012Q3=col5 _2012Q4=col6  _2012Y=col7 _YOY=col8 

             _2013Q1=col9 _2013Q2=col10 ));

run;

** PROC REPORT step that uses the "generic" column names;

You asked how to keep the "these columns" in the "same output", but I don't know what you mean by "these columns". What PROC REPORT code have you tried? What is the structure of your input data? Are you doing this as part of a stored process? Is this something in an EG project? Where is the "dynamic" data coming from?

What is your destination of choice for the output -- what kind of output do you want table or report or graph? Since you posted this in the GRAPH forum, where does the graph component come into play?

cynthia

Also, it looks like you have posted a version of this question in another forum. In your other posting, it looks like you want a dataset, not a report from PROC REPORT and not a graph.

https://communities.sas.com/message/144987#144987