BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Banu
Obsidian | Level 7

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

 

  

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
Astounding
PROC Star

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
Super User

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
Jade | Level 19

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1579 views
  • 0 likes
  • 4 in conversation