## report with dynamic columns

Frequent Contributor
Posts: 115

# report with dynamic columns

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.

Super Contributor
Posts: 644

## Re: report with dynamic columns

Posted in reply to sunilreddy

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

+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 ;

SAS Super FREQ
Posts: 9,365

## Re: report with dynamic columns

Posted in reply to sunilreddy

Hi:

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

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}
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(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(column)={background=cxeeeeee};
define _YOY /computed '2011/2012#YoY' f=comma12.2
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;

Discussion stats
• 2 replies
• 1084 views
• 0 likes
• 3 in conversation