Multi-Format Column

[PaigeMiller]

---

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

---

The program from @Kurt_Bremser requires NO changes to the code to run with 12 months. It doesn't get any simpler than that.

If it doesn't work, don't say "It doesn't work", show us the code you tried.

[mkeintz]

The topic title is "multi-format" column.  And the correct answer is a column can't have more than one format.

But it CAN have a multi-component format (as in sometimes %, sometimes integer).  You can't base those components on the value of the VARIABLE column, but it appears that for the odd values of VARIABLE, the M variables all range from zero-through-one, and for all others look like integers greater than one.  So define a different format for each of those ranges, as in:

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

proc format;
  value ff  0-1=[percent8.0]
            1<-high=[comma8.0];
run;

proc print data=sale noobs;
  format m: ff.;
run;

yielding:

variable	m1	m2	m3	m4	m5
1	52%	36%	98%	74%	99%
2	36	52	2	6	42
3	96%	55%	44%	70%	32%
4	65	8	24	6	21
5	63%	36%	54%	80%	70%

Edit note: the format above is the tweaked version.  It was:

proc format;
  value ff  1<-high=[comma8.0]
             other=[percent8.0];
run;

And yes, as @PaigeMiller observes, you must know that the rows that should have the percentage format have a range that does not intersect the range of the other rows.

[PaigeMiller]

This relies on certain assumptions not stated in the original question. Without knowing that these assumptions are valid, I would not recommend this solution.

[sasuser_8]

It's elegant but I have another variable in my DB wich is less than 0 without being a percentage 😞 

[quickbluefish]

The other option is to simply make all your M: columns character variables - this works fine and will allow you to use arrays as seems to be your preference.  If you output to Excel afterwards, it may or may not interpret them as numbers:

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;

data sale;
set sale;
length mon1-mon5 $15;
array mon {*} mon1-mon5;
array m {*} m1-m5;
do i=1 to dim(m);
	if mod(_N_,2) then mon[i]=put(m[i],percent8.1);
	else mon[i]=put(m[i],comma8.0);
end;
drop m1-m5 i;
run;

proc print data=sale noobs width=min; run;

...resulting in:

...obviously, you'll need to choose the appropriate formats for your data - for ex., if you have integers of 100M or larger, you'll need something longer than comma8.0.  Likewise if you have floating point numbers instead of all integers.

[mkeintz]

---

@sasuser_8 wrote:

It's elegant but I have another variable in my DB wich is less than 0 without being a percentage 😞 

---

"Less than 0 without being a percentage"?  Why is that a problem?   Did you see that my revised solution did not format negatives as percentages?  You could be even more specific, using:

proc format;
  value ff    0-1=[percent8.]
                 other=[comma8.0] ;

This depends on values of zero through one (and only zero through one) always being intended as percentages.  So far you have not said your data violate that rule.

[sasuser_8]

I didn't write it correctly.
I meant another variable between 0 and1 which is not a percentage.

[Ksharp · Accepted solution]

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 nowd;
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 call define(vname(v{i}),'format','percent8.0');
end;
endcomp;
run;

[sasuser_8]

It works well. Thanks 🙂