Format variable values based on values

[Banu]

Hi,

Need help to apply formats to SAS variables.

Below is the scenario.

I have table structure as below.

Name                          Amount

  A                                 4568442
 Acat1                           628727452.17 
 Acat2                           819125592.25 
 Acat3                           9491576104.14 
 Acat4                           221336147.01
 Aset1                           2026005 
 Aset2                           1247890       

I need Output as below. Needs to apply multiple formats to the input data based on variable value.

Could you please help me how to achieve same.

Name                          Amount

  A                                 4,568,442
 Acat1                           $628,727,452.17 
 Acat2                           $819,125,592.25 
 Acat3                           $9,491,576,104.14 
 Acat4                           $221,336,147.01
 Aset1                           2,026,005 
 Aset2                           1,247,890

[Astounding]

A variable can have just one format.  The best you can do is to create a new variable, based on the existing data.  For example:

data want;

set have;

select (name);

   when ('A') amount2 = put(amount, comma9.);

   when ('Acat1') amount2 = put(amount, dollar15.2);

   ... more WHEN statements for the remaining cases ...

   otherwise;

end;

run;

It's up to you to select the proper formats for each WHEN statement.

[ballardw · Accepted solution]

You can create custom formats that apply different appearance for ranges of values.

However if you mean the format has to change according to another variable, such as your Name variable, that is not possible. Is that what you are attempting?

You can create a text variable that is created conditional on the value of your name value such as

length newvar $ 16;

if name in ('Acat1' 'Acat2' 'Acat3' 'Acat4') then newvar=put(value,dollar16.2);

else if name in ('Aset1' 'Aset2') then newvar = put(value,comma10.);

Though if the "Amount" value actually has different meanings such as Acat are "Sales in dollars" and Aset are "numbers sold" they really should be different variables and then the format issue would be trivial.

[andreas_lds]

It is not possible to attach multiple formats to one variable. If you don't need the actual number in later calculations, you could use something like:

data work.narf;
   length Name $ 10 Amount 8 NumFormatted $ 40;

   input Name $ Amount;

   NumFormatted = left(putn(Amount, ifc(index(put(Amount, best.), '.') > 0, 'dollar15.2', 'comma15.')));

datalines;
 A 4568442
 Acat1 628727452.17 
 Acat2 819125592.25 
 Acat3 9491576104.14 
 Acat4 221336147.01
 Aset1 2026005 
 Aset2 1247890
 ;
 run;

Edit: Assuming the  dollar-format should be used on values having decimals, and comma-format for values without decimals.

[Banu]

Thank you Andrea for your info.

Now I have the required data in SAS dataset. Then I tried to generate excel sheet by using ODS tagsets excelxp,

excel sheet contains only Dollar values(like $1,234,976.10) but comma formatted values are vanished(as 123456).

I need both Dollar formatted and Comma formatted values in excel sheet by using ODS TAGSETS.

Current data:

39,442,499

$828,727,452.17

$519,125,592.25

$1,491,576,104.14

421,336,147

Once I use odstagsets excelxp. got output as below. But need output as above. Need both dollar formatted values and comma formatted values in my excel sheet. Please help me.

39442499

$828,727,452.17

$519,125,592.25

$1,491,576,104.14

421336147