report with dynamic columns

[sunilreddy]

i've below data in table.these numeric columns will change time to time, but column poistion will not change.
thse column contains total previous year(2011Y) and current year  quarter data(012Q1,2012Q2,2012Q3,2012Q4).

region_nm cu_nm 2011Y 2012Q1 2012Q2 2012Q3 2012Q4 2013Q1 2013Q2

----------------------------------------------------------------------------------------------------------------
REGIONX   blank    14      3       4     5       6      3      4
REGIONX    CUX    3    1       2     2       0      1      2
REGIONX    CUY    4    2       2     3       0      1      0

So i want output with below conditions

1) Add two new columns 2012Y (sum of 2012Q1,2012Q2,2012Q3,2012Q4) and 2011/2012YOY(diff of 2012Y and 2011Y) to existing table.
2) Print output as an attached format. also add sum of all CU_nms as attached.

Could you please help me to write the logic for this.

[RichardinOz]

Here is a general solution to the problem you pose.  It is based on my suggested data table format for your previous question.  The code is appended.

Program steps

1. Create source table.  Using data supplied in your question.  Numbers for 2011 have been assigned to Q4 without affecting the outcome of this example. Year and Quarter values are stored as strings for simplicity and efficiency.
2. Find last complete year.  This is the max value for year where data for Q4 exists.  The previous and next years are also calculated. (If no “next” year data exists, this will not disturb the process.)
3. Summarise data.  We want summaries for the years in question, by region, CU, year and quarter.  Some of the data is redundant but will be screened out in the next step.  The _type_ variable indicates the level of summarisation.
4. Add category and group labels.  Identify regional subtotals (ALL CU) and overall TOTALS per quarter and year.  Dummy YOY records are created as placeholders in the table.  The _name_ variable will be used for column headings by proc transpose.
5. Sort into order for transposition.  The sort ensures subtotals and totals come last at regional and overall level.
6. Transpose data into table form.  This spreads the selected quarterly and annual data across columns, instead of down rows.
7. Calculate YOY and clean up.  Calculate the YOY as required, add the subtotal and total labels and get rid of unwanted columns for cat, grp, and _name_.

Richard in Oz

/*    Calculate_totals_YOY.sas

*/

/*    Required calculation of region totals

    and totals by quarter and year,

    with year on year for the last

    complete year  */

/*    Richard C Carson

    Melbourne, Australia

    richard@arcol-analytics.com

    +61 412 836 040

    */

/*    Step 1: Create source table

*/

data have ;

    infile datalines dsd dlm='09'x ;

    informat   region_nm    $8.

		cu_nm	$3.
		year	$4.
		quarter	$1.
		number	best16.
		;
input	region_nm    $
		cu_nm	$
		year	$
		quarter	$
		number
		;

datalines ;

REGIONX		2011    4    14
REGIONX	CUX    2011    4    3
REGIONX	CUY    2011    4    4
REGIONX		2012    1    3
REGIONX	CUX    2012    1    1
REGIONX	CUY    2012    1    2
REGIONX		2012    2    4
REGIONX	CUX    2012    2    2
REGIONX	CUY    2012    2    2
REGIONX		2012    3    5
REGIONX	CUX    2012    3    2
REGIONX	CUY    2012    3    3
REGIONX		2012    4    6
REGIONX	CUX    2012    4    0
REGIONX	CUY    2012    4    0
REGIONX		2013    1    3
REGIONX	CUX    2013    1    1
REGIONX	CUY    2013    1    1
REGIONX		2013    2    4
REGIONX	CUX    2013    2    2
REGIONX	CUY    2013    2    0

;

/*    Step 2: Find last complete year

*/

Proc SQL noprint ;

    Select put(max(input(year,4.)),4.)

into    :year1

    From    have

    Where    quarter = '4'

;

Quit ;

%Put    NOTE: Last complete year is &year1.. ;

%Let    year0    =    %eval(&year1 - 1) ;

%Let    year2    =    %eval(&year1 + 1) ;

/*    Step 3: Summarise data    */

Proc Summary

		missing
		data    =    have ;

    where year in ("&year0", "&year1", "&year2") ;

By	year ;
Class	region_nm
		cu_nm
		quarter
		;
var	number ;
Output     out	=    totals
		sum     =
		;

Run ;

/*    Step 4: Add category and group labels    */

Data    totals2 ;

    Length    _Name_    $ 8 ;

    Set     totals ;

    By    year ;

    Where     (year = "&year0" and _type_ IN (0, 4, 6))   

	or     (year = "&year1")
	or    (year = "&year2" and _type_ IN (1, 3, 5, 7))
	;

    If    quarter > ' ' then _name_ = cat('Y', year, 'Q', quarter) ;

      else    _name_ = cat('Y', year) ; 

      Select (_type_) ;

	When (0,1)    cat    =    'TOTAL' ;
	When (2,3)		delete ;
	When (4,5)    grp    =    'ALL' ;
	Otherwise ;

    End ;

If	_name_    =    "Y&year1"

      Then

	Do ;
		Output ;
		_name_    =    CATT(_name_, 'YOY') ;
		Call missing (number) ;
		output ;
	End ;
Else	output ;

    Drop    _freq_ ;

Run ;

/*    Step 5: Sort into order for transposition     */

Proc Sort    Data    =    totals2 ;

By	cat
		region_nm
		grp
		cu_nm
		year
		descending    _type_
		quarter
		;

Run ;

/*    Step 6: Transpose data into table form

*/

Proc Transpose

		data    =    totals2
		out	=    required
		;
By	cat
		region_nm
		grp
		cu_nm
		;
Var	number ;

Run ;

/*    Step 7: Calculate YOY and clean up

*/

Data    want ;

set	required ;

    If    cat    > ' '

      Then    region_nm    =    cat ;

    If    grp    > ' '

      Then    cu_nm    =    grp ;

    Y&year1.YOY    =    SUM(Y&year1, -Y&year0) ;

    Drop    cat

		grp
		_name_
		;

Run ;

[Cynthia_sas]

Hi:

  You've posted versions of this question in a few different places:

https://communities.sas.com/thread/38919

and

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

and

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

  It's hard to understand what you want. One example of PROC REPORT code was already posted to do the color coding. You have another example of doing data manipulation. It seems like your data example and your description of what you want changes in every post. In a previous posting, someone asked you how you were creating your variables, since "2012Q1" is not a valid variable name in SAS -- are you using EG and is your form of reference '2012Q1'n???

  Do you need an output dataset (you have some suggested code for that) or do you need a report (there's been code posted for that)? If you need a report, what is your destination of choice? In one of your other postings, you showed some indenting and color coding that would not be available in LISTING output (for example). However, this report that you've posted looks very similar to the picture you posted in the GRAPH forum and it resembles the report with the CU values that you've shown in another forum posting. Do you need the red color coding that you showed in your other post?

  PROC REPORT can calculate columns for you. See the example below; I used some fake data, based on SASHELP.PRDSALE, since I'm still not clear on your data or what you mean by "dynamic" variables. But this program is just a variation on the previous PROC REPORT that I posted -- it shows having the _2012Y and the _YOY columns computed by PROC REPORT. And it still does color coding, as shown in your screenshot.

cynthia

data fakedata;
  set sashelp.prdsale;
   region_nm = region;
   cu_nm = substr(product,1,3);
   ** make some fake numbers;
   _2011Y = sum(actual *.75, predict*.86);
   _2012Q1 = actual/4;
   _2012Q2 = predict / 4;
   _2012Q3 = actual / 3.33;
   _2012Q4 = predict / 4.71;
   _2013Q1 = actual / 2.55;
   _2013Q2 = predict / 4.21;
   ** get different values for CU_NM for each REGION_NM;
   if region_nm = 'EAST' then do;
      if cu_nm in ('SOF','TAB') then output;
   end;
   else do;
      if cu_nm in ('DES','CHA') then output;
   end;
   format _numeric_ comma12.2;
run;

ods html file='c:\temp\doreport2.html' style=sasweb;
    title 'Sample Report';
    proc report data=fakedata nowd split='#'
         style(report)={rules=none frame=void cellspacing=0}
         style(header)={background=verydarkblue color=white
                        font_weight=bold};
      column region_nm cu_nm
             _2011Y _2012Q1 _2012Q2 
             _2012Q3 _2012Q4 _2012Y _YOY 
             _2013Q1 _2013Q2 ;
      define region_nm / group order=data ' ';
      define cu_nm / group order=data ' ';
      define _2011Y / sum '2011Y#REQ'
             style(header)={background=cxeeeeee color=black}
             style(column)={background=cxeeeeee};
      define _2012Q1 /sum '2012Q1#REQ' ;
      define _2012Q2  /sum '2012Q2#REQ'   ;
      define _2012Q3 /sum '2012Q3#REQ+F' ;
      define _2012Q4 /sum '2012Q4# F ' ;
      define _2012Y /computed '2012Y#REQ+F' f=comma12.2
             style(header)={background=cxeeeeee color=black}
             style(column)={background=cxeeeeee};
      define _YOY /computed '2011/2012#YoY' f=comma12.2
             style(header)={background=cxbbbbbb color=black}
             style(column)={background=cxbbbbbb};
      define _2013Q1 /sum '2013Q1# F ' ;
      define _2013Q2 /sum '2013Q2# F' ;
      compute _2012Y;
        _2012Y = sum(_2012Q1.sum, _2012Q2.sum, _2012Q3.sum, _2012Q4.sum);
      endcomp;
      compute _YOY;
        _YOY = _2011Y.sum - _2012Y;
      endcomp;
      break after region_nm / summarize;
      compute after region_nm;
        line ' ';
      endcomp;
    run;
ods _all_ close;