Percent8. format for specific report rows

[covershaker45]

Hello, I need assistance with proc report.  I'm trying to create a spreadsheet that contains numeric data with a format of 8. for all rows except  where variable category = 'Ratio', then I would like to use percent8. 

My data looks like:

Name	Title	Dept	Category	EOD1201	EOD1202	EOD1203	EOD1204	EOD1205	Change
Doe, John	CEO	DEP	AMR	5	6	10	12	15	3
Doe, Jan	VP	RSK	Ratio	.23	.25	.75	.88	.25	0
Smith, Bob	SVP	SMB	MDR	7	8	9	9	9	0
Smith, Mike	Associate	CHK	SUB	6	6	8	8	7	1

I want it to look like:

Name	Title	Dept	Category	EOD1201	EOD1202	EOD1203	EOD1204	EOD1205	Change
Doe, John	CEO	DEP	AMR	5	6	10	12	15	3
Doe, Jan	VP	RSK	Ratio	23%	25%	75%	88%	25%	0
Smith, Bob	SVP	SMB	MDR	7	8	9	9	9	0
Smith, Mike	Associate	CHK	SUB	6	6	8	8	7	1

Each day a new column (Variable) is added to reflect the previous days information.   Few questions:

1.  How can I use proc report to update the format?

2. How should the column statement be coded to account for new columns added?

3. Do I use the compute to update the format?

Current proc report looks like:

proc report data=New_Report nowd

     style(header)={font_face="Arial" font_size=8pt color=white backgroundcolor=CX000000 tagattr='wraptext:no' width=100%}
     style(report)={font_face="Arial" font_size=8pt tagattr='wraptext:no' width=100%}
     style(lines)={just=left font_face=Arial font_size=8pt tagattr='wraptext:no' width=100%}
     style(column)={font_face="Arial" font_size=8pt tagattr='wraptext:no' width=100%};

run;

Any assistance would really be appreciated.

[ballardw]

Show some actual starting data as in a data step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

IF, as I believe, you are adding variables constantly then you need to reconsider things as to address specific column values conditionally you will need to rewrite the report code for every added variable. NOT the way to go.

The data should be structured such that one variable holds the value of the column heading and another holds the value. Then you use the variable holding the column heading as an ACROSS variable and the code then has a chance of working as data is added. An example:

proc report data=sashelp.class;
   columns name sex, height;
   define sex /across "";
run;

Note that the values of the Sex variable, F and M, make up column headings. The "" in the Define says to not use the variable name as a column heading.

You do not describe what "change" should be. Since you have changing numbers of columns that almost certainly would lead to preprocessing the data as multiple changing column references are not going to be nice or clean inside proc report code. Also it appears that you do not want to calculate "change" for the Ratio rows. If that is the case you should state so.

[Astounding]

Maybe, just maybe ...

If the "Ratio" row is the only row that takes on values between 0 and 1, you could try creating a format.  This is untested code but looks like it should work:

proc format;
value ratio 0 >- <1 = [percent8.];
run;

Then apply the format later using:

format eod12: ratio.;

[Tom · Accepted solution]

You can use CALL DEFINE to change the format attached to column(s).

Here is example using your existing dataset:

data have ;
  input Name &$ (Title Dept Category) ($) EOD1201 EOD1202 EOD1203 EOD1204 EOD1205 Change ;
cards;
Doe, John    CEO       DEP AMR  5 6 10 12 15 3
Doe, Jan     VP        RSK Ratio .23 .25 .75 .88 .25 0
Smith, Bob   SVP       SMB MDR   7 8 9 9 9 0
Smith, Mike  Associate CHK SUB   6 6 8 8 7 1
;


proc report data=have;
  column Name Title Dept Category EOD1201 EOD1202 EOD1203 EOD1204 EOD1205 Change;
  compute change ;
    if category='Ratio' then do col=5 to 10;
      call define(col,'format','percent8.'); 
    end;
  endcomp;
run;

To make it more dynamic change the structure of the dataset so that the values EOD1201, EOD1202, etc are in a variable instead of in the metadata.  For example you could use PROC TRANPOSE on your current data.

proc transpose data=have out=tall ;
  by name--category notsorted;
  var _numeric_;
run;

Now you can use _NAME_ as an across variable.

proc report data=tall;
  column Name Title Dept Category col1,_name_ ;
  define name--category / group order=data ;
  define _name_ / across order=data ' ';
  define col1 / sum ' ' width=8 ;
  compute col1 ;
    if category='Ratio' then call define(_col_,'format','percent8.'); 
  endcomp;
run;

Results:

  Name      Title     Dept      Category    EOD1201    EOD1202    EOD1203    EOD1204    EOD1205     Change
  Doe, Joh  CEO       DEP       AMR               5          6         10         12         15          3
  Doe, Jan  VP        RSK       Ratio          23%        25%        75%        88%        25%         0%
  Smith, B  SVP       SMB       MDR               7          8          9          9          9          0
  Smith, M  Associat  CHK       SUB               6          6          8          8          7          1