Proc Report display different formats in same columns

[Ronein]

Hello

Please see output data set that I want to display as the final report.

The structure of the wanted summary table should be same (long structure) but with the following format:

raw1: comma18.

raw2: comma18.

raw3: percent8.2

raw4: percent8.2

raw5: percent8.2

and then again

raw6: comma18.
raw7: comma18.
raw8: percent8.2
raw9: percent8.2
raw10: percent8.2

and so on...

What is the way to create the report please?

Data wanted;
input var $ x;
cards;
Revenue 28520
Expenses 1347
PCT1 0.047230014
PCT2 0.7728285078
PCT3 0.7803363972
Revenue 32315
Expenses 490
PCT1 0.0151632369
PCT2 0.7693877551
PCT3 0.7714660211
;
Run;

[PaigeMiller]

Such formatting in a report would be much easier to program if you transposed the table, so every row in each column had the same format. If the business needs are so strict that this is not a reasonable approach, then to obtain different formats in each row you would need to include many IF statements in a COMPUTE block in PROC REPORT that change the format by row.

[Ronein]

I will start from beginning.

Please see the "have" data set and the "wanted" report.

As  can see the wanted report has  a column with different formats.

What is the way to create this report? via proc report or another way

Data Have;
input Model $ Revenue Expenses PCT1 PCT2 PCT3;
cards;
Model1 28520 1347 0.047230014 0.7728285078 0.7803363972
Model2 32315 490 0.0151632369 0.7693877551 0.7714660211
;
Run;

Data wanted_Report;
input Model $var $ x;
cards;
Model1 Revenue 28520
Model1 Expenses 1347
Model1 PCT1 4.72%
Model1 PCT2 77.28%
Model1 PCT3 78.03%2
Model2 Revenue 32315
Model2 Expenses 490
Model2 PCT1 15.16%
Model2 PCT2 76.93%
Model2 PCT3 77.14%
;
Run;

[PaigeMiller]

Yes, I understand the format of your data and the desired format of the report. My point was: it is much easier to change the format of the report, and then you don't have to program IF statements by row, and then you get the same information in the report, but the layout of the report is different. But, if you want the more labor intensive method, for this case, the @Ksharp has shown how to do this. In this simple example, the actual programming to produce the proper IF statements in the COMPUTE block is pretty simple; in more advanced real-life data problems, the creation of the proper IF statements in the COMPUTE block can get tedious and time consuming.

[RichardDeVen]

FORMAT your variables, add a dummy character variable, and then TRANSPOSE by MODEL.  When the variables being pivoted (per VAR) are mixed type, the resultant pivot column will be of character type and contain the formatted values.

Example:

Data Have;
input Model $ Revenue Expenses PCT1 PCT2 PCT3;
format PCT: PERCENT8.2;
format rev: exp: comma9.;
s = '*';
cards;
Model1 28520 1347 0.047230014 0.7728285078 0.7803363972
Model2 32315 490 0.0151632369 0.7693877551 0.7714660211
;

proc transpose data=have out=report(rename=(_name_=Var col1=X) where=(X ne '*')) ;
  by model;
  var revenue expenses pct1-pct3 s;
  label Var = ' ';
run;

ods html file='report.html';

  proc report data=report;
    define x / style=[textalign=right];
  run;

ods html close;

Output data

ODS Output

[PaigeMiller]

s = '*';

Aha, very sneaky.

Of course, this works in the simplified example presented. In more complicated examples, this may not work, especially if you want to add columns or do other math in the COMPUTE block.

[Ksharp]

Data wanted;
input var $ x;
cards;
Revenue 28520
Expenses 1347
PCT1 0.047230014
PCT2 0.7728285078
PCT3 0.7803363972
Revenue 32315
Expenses 490
PCT1 0.0151632369
PCT2 0.7693877551
PCT3 0.7714660211
;
Run;
proc report data=wanted nowd;
column var x;
define var/display;
compute x;
 if var =: 'PCT' then call define(_col_,'format','percent8.2');
  else call define(_col_,'format','comma18.');
endcomp;
run;

[Kurt_Bremser]

Or compute a character variable:

Data Have;
input Model $ Revenue Expenses PCT1 PCT2 PCT3;
cards;
Model1 28520 1347 0.047230014 0.7728285078 0.7803363972
Model2 32315 490 0.0151632369 0.7693877551 0.7714660211
;

proc transpose
  data=have
  out=long (rename=(_name_=var col1=value))
;
by model;
var _numeric_;
run;

proc report data=long;
column model var value=val x;
define model / display;
define var / "var" display;
define val / noprint;
define x / computed right;
compute x / character length=10;
  if var =: "PCT"
  then x = put(val,percent10.2);
  else x = put(val,10.);
endcomp;
run;