Can a column have two different formats?

[deleted_user]

Hi,

I am trying to conditionally assign a format to a column based on another columns values.

Here is a simple example of what I am trying to do:

Given this data set:

Range Value
1-5 1
Test 2



Conditionally assign a format to Value based on the value of Range, i.e.

if range = '1-5' then ... format Value to a percent i.e. 1%
if range = 'test' then ... format Value to a dollar i.e. $2

So my simple table would look like:

Range Value
1-5 1%
Test $2

Can this be done? Any hints or suggestions would be greatly appreciated!

Thanks
Rich

[Flip]

You can create a third column with the formatted value.

if range = '1-5' then newcol = put(Value , format1.);
if range = 'test' then newcol = put(Value , format2.);

[deleted_user]

Thanks,

This works great!

Rich

[Ksharp]

Yes. Flip is right.
You also can make a variable to hold the format name. then use putn() or inputn() to make a transformation.


[pre]
data site_traffic_overview;
infile datalines dlm=',' dsd;
input range $ value ;
datalines;
"1-5",.53
"test",5.89
"test",5.89
;
run;

data result;
length format _value $ 20 ;
set site_traffic_overview;
if range='1-5' then format='percent5.2';
else if range='test' then format='dollar10.2';
_value=putn(value,format);
run;
[/pre]
Ksharp

[r0oki3]

When I try to run your example, it is changing the format of value from numeric to character and not retaining the numeric type. How can I get the _value to be of numeric type?

[deleted_user]

Rich,

Does the column in question actually have such different values (something that would be a percent versus dollars)?

The short answer to your question, is: no, a given variable can only have one format associated with it at a time; however, I suspect that something else might be done if I understood the situation better. For example, although it would be a space hog, you could create a new column that was the appropriately formatted values of the original column.

[deleted_user]

Thanks for your response kmg.

Unfortunately, the column in question does have different values, which is out of my control.

I created a new column and it works fine. Probably not the best way, but it works!

Thanks again,
Rich

[Cynthia_sas]

Hi:
There's a difference between associating a format with a variable in the descriptor portion of the SAS dataset and using a format for report purposes. For the purposes of the descriptor portion of the data set, a variable can only have 1 format assigned. If you want to create a new variable in a DATA step program, and the new variable value would be the result of using one format or the other, then you would use the PUT function, as shown. But the downside of this is that if the conditions change or the format changes, you have to go back and change the value of that new variable so it's in sync. The good news here is that the SAS dataset is not your only mechanism for getting what you want.

If you only needed the differing formats for reporting purposes, PROC REPORT will let you conditionally assign a format to one variable based on another variable's value using the CALL DEFINE statement. You would have a COLUMN statement and COMPUTE block something like this -- let's say you want to change the format for var3 based on the value of var2:
[pre]
proc report data=mydata nowd;
column var1 var2 var3 var4;
.... more code ....
compute var3;
if var2 = 'Something' then do;
call define(_COL_,'FORMAT','dollar8.');
end;
else call define(_COL_,'FORMAT','comma8.');
endcomp;
[/pre]

Your best bet for help on this is to look at the PROC REPORT documentation and search for some of the papers on PROC REPORT. Things might get a bit more complex if VAR2 were a GROUP or ORDER item on the report, but the general concept would still be the same.

cynthia

[deleted_user]

Thanks for the input. I had played around with proc report but couldn't get my syntax correct. Thanks for the example, this should help.

Rich

[saspert]

Hi Cynthia,
I was researching for exactly this issue - displaying different formats for the same column in proc report. I tried your suggestion, but I have missing values for all the cells. I am not sure why -

PROC REPORT DATA=SITE_TRAFFIC_OVERVIEW;
COLUMN DESC VISITS PAGE_VIEWS;
DEFINE DESC / DISPLAY "Site Traffic";
DEFINE VISITS / COMPUTED "Visits";
DEFINE PAGE_VIEWS / COMPUTED "Page Views";
COMPUTE VISITS;
IF DESC="Total" THEN
DO;
CALL DEFINE(_C2_,'FORMAT','z6.');
end;
ENDCOMP;
RUN;


The data set SITE_TRAFFIC_OVERVIEW looks like this -
DESC VISITS PAGE_VIEWS
Total 53.00 314.00
Average Per Day 5.89 34.89
Average Per Day Per Member 5.89 34.89

Do you have any suggestions that I can try out?

Thanks,

[Cynthia_sas]

Hi:
If VISITS and PAGE_VIEWS are variables in the dataset, I don't know why you have them defined with a usage of "COMPUTED" -- you can still use them in a COMPUTE block even if they have a usage of DISPLAY. Also, without any ACROSS variables, the absolute column number of _C2_ is unnecessary. The simple reference _COL_ should work just fine.

The code below worked for me, given the fake data that I created from your description.

cynthia
[pre]
data site_traffic_overview;
length DESC $30;
infile datalines dlm=',' dsd;
input DESC $ VISITS PAGE_VIEWS;
return;
datalines;
"Total",53.00,314.00
"Average Per Day",5.89,34.89
"Average Per Day Per Member",5.89,34.89
;
run;

ods listing close;
ods html file='c:\temp\diff_fmt.html' style=sasweb;
PROC REPORT DATA=SITE_TRAFFIC_OVERVIEW nowd;
COLUMN DESC VISITS PAGE_VIEWS;
DEFINE DESC / order "Site Traffic" order=data;
DEFINE VISITS / Display "Visits";
DEFINE PAGE_VIEWS / display "Page Views";
COMPUTE VISITS;
IF DESC="Total" THEN DO;
CALL DEFINE(_col_,'FORMAT','z6.');
end;
ENDCOMP;
RUN;
ods html close;
[/pre]

[saspert]

Thanks Cynthia - it worked!

[Peter_C]

Rich
as has been stated you cannot define more than one format to a column, but ...
an earlier post in the EG forum shows a way to transpose several variables (with different formats) into one column where their separate formats are preserved
http://support.sas.com/forums/thread.jspa?messageID=48697븹

peterC

[nj_sas]

Peter

The below link isn't working. I would like to know details on the transposing several variables with different formats into one column where their separate formats are preserved. Thanks

[art297]

I would try the link again.  It just worked for me.

Correction: Use the link that Peter mentions in the following post.  I just discovered that the link I thought had worked had actually only returned me to the current thread.