topic Re: Format variable values based on values in SAS Programming

Format variable values based on values

Banu — Wed, 24 Jan 2018 14:33:27 GMT

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

Re: Format variable values based on values

Astounding — Wed, 24 Jan 2018 14:58:58 GMT

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.

Re: Format variable values based on values

ballardw — Wed, 24 Jan 2018 15:02:38 GMT

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.

Re: Format variable values based on values

andreas_lds — Wed, 24 Jan 2018 15:13:58 GMT

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.

Re: Format variable values based on values

Banu — Thu, 25 Jan 2018 09:57:32 GMT

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