topic Re: report with dynamic columns in ODS and Base Reporting

report with dynamic columns

sunilreddy — Sun, 18 Nov 2012 12:00:07 GMT

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.

Re: report with dynamic columns

RichardinOz — Mon, 19 Nov 2012 04:41:26 GMT

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

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.
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.)
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.
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.
Sort into order for transposition. The sort ensures subtotals and totals come last at regional and overall level.
Transpose data into table form. This spreads the selected quarterly and annual data across columns, instead of down rows.
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 ;

Re: report with dynamic columns

Cynthia_sas — Mon, 19 Nov 2012 17:13:31 GMT

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;