topic Multi-Format Column in SAS Programming

Multi-Format Column

sasuser_8 — Fri, 29 Nov 2024 23:44:37 GMT

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 ?

Re: Multi-Format Column

PaigeMiller — Sat, 30 Nov 2024 00:42:07 GMT

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

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

Re: Multi-Format Column

PaigeMiller — Sat, 30 Nov 2024 11:35:32 GMT

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.

Re: Multi-Format Column

PaigeMiller — Sat, 30 Nov 2024 16:59:53 GMT

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

Re: Multi-Format Column

Kurt_Bremser — Sun, 01 Dec 2024 11:29:29 GMT

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;

Re: Multi-Format Column

mkeintz — Mon, 02 Dec 2024 03:46:40 GMT

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.

Re: Multi-Format Column

PaigeMiller — Sun, 01 Dec 2024 22:24:35 GMT

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

Re: Multi-Format Column

sasuser_8 — Mon, 02 Dec 2024 19:41:05 GMT

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

Re: Multi-Format Column

sasuser_8 — Mon, 02 Dec 2024 19:43:51 GMT

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

Re: Multi-Format Column

Kurt_Bremser — Mon, 02 Dec 2024 20:31:41 GMT

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

Re: Multi-Format Column

sasuser_8 — Mon, 02 Dec 2024 21:56:30 GMT

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

Re: Multi-Format Column

PaigeMiller — Mon, 02 Dec 2024 22:18:48 GMT

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.

Re: Multi-Format Column

sasuser_8 — Mon, 02 Dec 2024 22:18:42 GMT

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;

Re: Multi-Format Column

PaigeMiller — Mon, 02 Dec 2024 22:20:56 GMT

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

Re: Multi-Format Column

sasuser_8 — Mon, 02 Dec 2024 22:34:34 GMT

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.

Re: Multi-Format Column

Kurt_Bremser — Mon, 02 Dec 2024 23:18:50 GMT

@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?

Re: Multi-Format Column

Kurt_Bremser — Mon, 02 Dec 2024 23:24:07 GMT

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.

Re: Multi-Format Column

quickbluefish — Tue, 03 Dec 2024 01:32:38 GMT

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.

Re: Multi-Format Column

Ksharp — Tue, 03 Dec 2024 02:22:13 GMT

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;

Re: Multi-Format Column

mkeintz — Tue, 03 Dec 2024 05:09:58 GMT

@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.

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%

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%

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%