topic Re: Proc Report; Mixing formats in variables in SAS Procedures

Proc Report; Mixing formats in variables

Sennahlake — Wed, 18 Jun 2014 10:00:08 GMT

Hi!

I would like to get some help regarding proc report.

Below is my dataset;
t1 Sum1 Sum2 Sum3

Totalt 0,1 0,25 0,13

Borjade 2 5 5

Slutade 8 20 8

I would like to get a report that look like this and that the observation "Totalt" has a format with percentage and the other observations have another format;

t1 Sum1 Sum2 Sum3

Totalt 10% 25% 13%

Borjade 2 5 5

Slutade 8 20 8

My problem is that I can’t mix formats in the variables, I have tried to compute a dummy variable without success.
Does anyone knows what I’m doing wrong?
Below is my code.

ods listing close;

option missing='' nonumber nodate topmargin=0.5in leftmargin=0.5in rightmargin=0.4in papersize=(29.7cm 21.0cm) NOQUOTELENMAX;

ods escapechar="~";

ODS HTML close;

ods pdf bookmarkgen=no file="G:\test\report.pdf" notoc;

proc report data=report nowd completerows contents="" nocenter;

columns t1 sum1 sum2 sum3 dummy;

define t1 / style(column)={cellwidth=100};

define sum1 / style(column)={cellwidth=100};

define sum2 / style(column)={cellwidth=100};

define sum3 / style(column)={cellwidth=100 };

define dummy / noprint;

compute dummy;

if _NAME_ = 'Totalt' then do;

call define(_col_, 'format', 'percent9.2');

end;

endcomp;

run;

title;

ods pdf close;

ods listing close;

Best Regards

Hannes

Re: Proc Report; Mixing formats in variables

RW9 — Wed, 18 Jun 2014 10:18:04 GMT

Just my preference, I would convert all the columns into character then put that in the report. This is just a preference as I like my underlying data to look more or less like the output.

It looks like you have character data anyways as the comma is not valid in numerics:

Totalt 0,1 0,25 0,13Totalt 0,1 0,25 0,13

Re: Proc Report; Mixing formats in variables

Sennahlake — Wed, 18 Jun 2014 10:23:05 GMT

Sorry, my bad.

The variables Sum1, Sum2 & Sum3 has dots instead of commas.

My dataset looks like this;

t1 Sum1 Sum2 Sum3

Totalt 0.1 0.25 0.13

Borjade 2 5 5

Slutade 8 20 8

Re: Proc Report; Mixing formats in variables

Cynthia_sas — Wed, 18 Jun 2014 12:05:17 GMT

Hi:

You should investigate using CALL DEFINE in a COMPUTE block. CALL DEFINE allows you to change the format for a column very easily. Consider the following example. The overall format is set to 9.3 for the mean of HEIGHT and WEIGHT. Then, for age=12, the format is set to percent9.2 (completely silly format); for age=13, the format is set to comma9. (no decimals) and for age=16, the format is set to comma9.1 (1 decimal place). You should be able to run the code since it uses SASHELP.CLASS.

Cynthia

ods html file='c:\temp\diff_fmt.html';

proc report data=sashelp.class nowd;
column age ('Average' height weight);
define age / group;
define height / mean f=comma9.3;
define weight / mean f=comma9.3;
compute height;
if age = 12 then do;
     call define(_col_,'FORMAT','percent9.2');
end;
if age = 14 then do;
     call define(_col_,'FORMAT','comma9.');
end;
if age = 16 then do;
     call define(_col_,'FORMAT','comma9.1');
end;
endcomp;
compute weight;
if age = 12 then do;
     call define(_col_,'FORMAT','percent9.2');
end;
if age = 14 then do;
     call define(_col_,'FORMAT','comma9.');
end;
if age = 16 then do;
     call define(_col_,'FORMAT','comma9.1');
end;
endcomp;

run;
ods _all_ close;

Re: Proc Report; Mixing formats in variables

RW9 — Wed, 18 Jun 2014 12:11:20 GMT

Again, just my preference:

data want (drop=i);

set have;

array sum_c{3} $200.;

array sum{3};

do I=1 to 3;

if t1="Totalt" then sum_c{I}=strip(put(sum{I},percent9.2));

else sum_c{I}=strip(put(sum{I},best.));

end;

run;

proc report ....

columns sum_c1-sum_c3;

...

Re: Proc Report; Mixing formats in variables

Sennahlake — Wed, 18 Jun 2014 12:14:59 GMT

Thanks Cynthia, that was such an easy way to solve my problem!

Re: Proc Report; Mixing formats in variables

Ksharp — Wed, 18 Jun 2014 13:01:34 GMT

data report;
input t1 $            Sum1     Sum2     Sum3 ;
cards;
Totalt    0.1          0.25        0.13
Borjade  2              5              5
Slutade 8              20           8
;
run;

ods listing close;
option missing='' nonumber nodate topmargin=0.5in leftmargin=0.5in rightmargin=0.4in papersize=(29.7cm 21.0cm)  NOQUOTELENMAX;
ods escapechar="~";
ODS HTML close;
ods pdf bookmarkgen=no file="report.pdf" notoc;
 
proc report data=report nowd completerows contents="" nocenter;
    columns t1 sum1 sum2 sum3 ;
    define t1 /   style(column)={cellwidth=100};
    define sum1 /  style(column)={cellwidth=100};
    define sum2 /   style(column)={cellwidth=100};
    define sum3 /   style(column)={cellwidth=100 };
 
    compute sum3;
        if t1 = 'Totalt' then do;
                call define(_col_, 'format', 'percent9.2');
            end;
    endcomp;
       compute sum2;
        if t1 = 'Totalt' then do;
                call define(_col_, 'format', 'percent9.2');
            end;
    endcomp;
       compute sum1;
        if t1 = 'Totalt' then do;
                call define(_col_, 'format', 'percent9.2');
            end;
    endcomp;

run;
 
title;
ods pdf close;
ods listing close;

Xia Keshan