Multi-Format Column

[sasuser_8]

Hi,

I have this table:

DATA SALE;
	INPUT VARIABLE M1 M2 M3 M4 M5;
	CARDS;

1  0.52 0.36 0.98 0.74 0.99 
2  36 52 2 6 42 
3  0.96 0.55 0.44 0.7 0.32
4  65 8 24 6 21
5  0.63 0.36 0.54 0.8 0.7
;
RUN;

I would like to get the following result: if the variable is 1, 3 or 5 then the format of the cell is displayed as a percentage like this:

How can I do this ?

[PaigeMiller]

PROC REPORT will provide the formatting you want. https://communities.sas.com/t5/ODS-and-Base-Reporting/PROC-REPORT-different-formats-for-different-ro...

Note: this provides a report, you cannot have a SAS data set with different formatting in a column.

[PaigeMiller]

data sale2;
	set sale;
	array m m1-m5;
	if mod(variable,2)=0 then do i=1 to 5;
	    m(i)=m(i)/100;
    end;
run;
proc report data=sale2;
	columns variable m1-m5;
	define variable/display;
	define m1/display;
	define m2/display;
	define m3/display;
	define m4/display;
	define m5/display;
	compute m1;
		if mod(variable,2)=0 then do;
           call define('m1','format','percent8.0');
        end;
    endcompute;
run;

Two things to note above:

• the percents (0 to 100 scale) in your original data need to be converted to proportions (0 to 1 scale) in your data in order for this to work, so we divide by 100
• I have only shown how to change column M1 to have the percent format. I leave it to you as a homework assignment to make this work for the other columns.

[PaigeMiller]

Correction to the above code. I was making the even numbered rows the percents, however you wanted the odd numbered rows to be the percents. So, no need to divide by 100, take that out. Instead of

mod(variable,2)=0

in both place where it appears, you want

mod(variable,2)=1

[sasuser_8]

 Is it possible to add an ARRAY in the PROC REPORT so as not to repeat the code for the 12 months ?

[Kurt_Bremser]

What do you mean by "12 months"? There is no variable containing date values in your dataset.

[sasuser_8]

I would like to insert an ARRAY in the PROC REPORT so as not to have to do the COMPUTE for each of my variables (m1,m2,m3,m4,m5).

As we can see in this example but I'm not able to do it:
https://support.sas.com/kb/43/765.html

[PaigeMiller]

The code from @Kurt_Bremser above makes this easy. No alteration to the code is needed if you have 12 months instead of 5. Better to work from a long data set than a wide data set.

---

@sasuser_8 wrote:

I would like to insert an ARRAY in the PROC REPORT so as not to have to do the COMPUTE for each of my variables (m1,m2,m3,m4,m5).

As we can see in this example but I'm not able to do it:
https://support.sas.com/kb/43/765.html

---

Please don't tell us "I'm not able to do it" and then provide no other information. We don't know what you tried. We have no idea what went wrong. If there are errors in the log, show us the LOG. If the results are not what you want, show us the code, show us the incorrect output, and explain exactly what you want.

[sasuser_8]

I tried this but the array is not recognized.
ERROR: Invalid column specification in CALL DEFINE.



DATA SALE;
INPUT VARIABLE M1 M2 M3 M4 M5;
CARDS;

1 0.52 0.36 0.98 0.74 0.99
2 36 52 2 6 42
3 0.96 0.55 0.44 0.7 0.32
4 65 8 24 6 21
5 0.63 0.36 0.54 0.8 0.7
;
RUN;


proc report data=sale;
columns variable m1-m5 dummy;
define variable/display;
define m1/display;
define m2/display;
define m3/display;
define m4/display;
define m5/display;
define dummy / computed noprint;

compute dummy;
array v m1-m5;

do I=1 to dim(v);
if variable in (1,3,5) then
do;
call define('V[I]','format','percent8.0');
end;
end;
endcomp;
run;

[PaigeMiller]

The code from @Kurt_Bremser above makes this easy. No alteration to the code is needed if you have 12 months instead of 5.

[Kurt_Bremser]

---

@sasuser_8 wrote:

I would like to insert an ARRAY in the PROC REPORT so as not to have to do the COMPUTE for each of my variables (m1,m2,m3,m4,m5).

As we can see in this example but I'm not able to do it:
https://support.sas.com/kb/43/765.html

---

No need for an array. Both @PaigeMiller's and my code work for all the M: variables. Just try it.

But what has that got to do with months?

[RichardAD]

There is no array in Proc REPORT for what you want.

You can transform the data into a categorical and use PROC REPORT across features .

DATA HAVE;
  INFILE CARDS MISSOVER ;
	INPUT VARIABLE M1-M12 ;
	CARDS;
1  0.52 0.36 0.98 0.74 0.99 
2  36 52 2 6 42 
3  0.96 0.55 0.44 0.7 0.32
4  65 8 24 6 21
5  0.63 0.36 0.54 0.8 0.7
;

proc transpose data=have out=have_t ;
  by variable ;
  var m1-m12 ;
run ;

ods html file='report.html' style=plateau ;
proc report data=have_t ;
  column variable _name_,col1;
  define variable / ' ' group ;
  define _name_ / across order=data;
  define col1 / ' ' sum ;
  compute col1 ;
    if mod(variable,2) = 1 then do ;
      call define (_col_, 'format', 'percent5.'); 
    end ;
  endcomp ;
run ;
ods html close ;

[Kurt_Bremser]

To show how a long layout can make coding simpler:

data sale;
input variable m1-m5;
datalines;
1  0.52 0.36 0.98 0.74 0.99 
2  36 52 2 6 42 
3  0.96 0.55 0.44 0.7 0.32
4  65 8 24 6 21
5  0.63 0.36 0.54 0.8 0.7
;

proc transpose data=sale out=long;
by variable;
var m:;
run;

proc report data=long;
column variable col1,_name_;
define variable / group;
define col1 / "" analysis;
define _name_ / "" across;
compute col1;
  if mod(variable,2) = 1 then call define(_col_,"format","percent8.0");
endcomp;
run;

[sasuser_8]

I tried with a colleague to transpose this method into my real program but it is too complicated for us.

The simplest thing for me would be to do the compute with an array, if that's possible.

[Kurt_Bremser]

Then show your "real program". And your "real" data structure. We can only help you with stuff we see, we're not in the mentalist business.