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

Hello,

In SAS Enterprise Guide, how can I change a column to display to the "thousandth"?  For example, if a number is $2,534,000 - I would like the result to show $2.534. 

I know I can divide a column by 100,000 but there are 60 columns and I don't want to do that for all 60 columns.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

I think you need to consider these two Cautions from the documentation.

CAUTION:

The picture must be wide enough for the prefix and the numbers.

In this example, if the value –45.00 were formatted with NOZEROS., then the result would be 45.00 because it falls into the first range, low – –1, and the picture for that range is not wide enough to accommodate the prefixed minus sign and the number.

CAUTION:

The picture must be wide enough for an additional digit if rounding a number adds a digit to the number.

For example, the picture for the number .996 could be ‘99’ (prefix ‘.’ mult=100). After rounding the number and multiplying it by 100, the resulting number is 100. When the picture is applied, the result is .00, an inaccurate number. In order to ensure accuracy of numbers when you round numbers, make the picture wide enough to accommodate larger numbers.

I don't think you need but one range for positive numbers.

proc format;
  
picture million(round) 0-1e9=' 000009.9' (prefix='$' mult=0.00001);
   run;
data null ;
   a = 49999;
   b =
70000;
   c =
100000;
   d =
1e6;
  
x = 19875000 ;
   y = 200000000;
  
format _all_ million. ;
   put (_all_)(=best.);
   put (_all_)(=million.);
   run;


a=49999 b=70000 c=100000 d=1000000 x=19875000 y=200000000

a=$0.0 b=$0.1 c=$0.1 d=$1.0 x=$19.9 y=$200.0

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well you could try using arrays:

data want;

     set have;

/* Way 1 where columns not the same = array myarray{60} var1 var2 var3 etc. - long winded */

     array var{60};   /* This assumes the variables are named var1 var2 etc. */

     do I=1 to 60;

          var{I}=var{I}/100000;

     end;

run;

Alternatively you could generate the either by macro or call execute.

data_null__
Jade | Level 19

This can be done with a PICTURE FORMAT and the appropriate multiplier applied to the range.  See the documentation I believe there is an example that is just what you are looking for or very close.

jakarman
Barite | Level 11

You probably have a numeric dollar format that has a format applied to that showing the $ ...... SAS(R) 9.4 Formats and Informats: Reference

With proc format you can define your own one with a multiply-factor.   Just change the formats not the data.  Base SAS(R) 9.4 Procedures Guide, Second Edition

---->-- ja karman --<-----
Cynthia_sas
SAS Super FREQ

Hi:

  Data_Null and Jaap are recommending the right way to deal with this issue -- changing the data is unnecessary. PROC FORMAT will do this for you and the documentation has an example of creating the "BIGMONEY." format for an example such as yours: Base SAS(R) 9.4 Procedures Guide, Second Edition (The example is entitled "Example 3: Creating a Picture Format for Large Dollar Amounts").

cynthia

jen123
Fluorite | Level 6

Thanks everyone for the responses.  Cynthia - thanks for the link to the Big Money example.  That helped, but I stil lhave an issue.  Here is my code.  The format works for  x and y.  However, a, b and c do not.  I should get:

a = $0.0

b = $.01

c= $.01

Instead I get:

a= $99.0

b= $1

c=$1

Your assistance is greatly appreciated!

********************

proc format;

   picture million (round fuzz=0)

    0 -< 50000 = '00.0' (prefix='$' mult=0.0)

  50000 -< 1000000 = '00.0' (prefix='$' mult=0.00001)

  1000000 -< 1000000000='0000.0' (prefix='$' mult=.00001);

   run;

  data null ;

   a = 49999;

   b = 70000;

   c = 100000;

   x = 19875000 ;

   y = 200000000;

   format a million. ;

   format b million. ;

   format c million. ;

   format x million. ;

   format y million. ;

run ;

data_null__
Jade | Level 19

I think you need to consider these two Cautions from the documentation.

CAUTION:

The picture must be wide enough for the prefix and the numbers.

In this example, if the value –45.00 were formatted with NOZEROS., then the result would be 45.00 because it falls into the first range, low – –1, and the picture for that range is not wide enough to accommodate the prefixed minus sign and the number.

CAUTION:

The picture must be wide enough for an additional digit if rounding a number adds a digit to the number.

For example, the picture for the number .996 could be ‘99’ (prefix ‘.’ mult=100). After rounding the number and multiplying it by 100, the resulting number is 100. When the picture is applied, the result is .00, an inaccurate number. In order to ensure accuracy of numbers when you round numbers, make the picture wide enough to accommodate larger numbers.

I don't think you need but one range for positive numbers.

proc format;
  
picture million(round) 0-1e9=' 000009.9' (prefix='$' mult=0.00001);
   run;
data null ;
   a = 49999;
   b =
70000;
   c =
100000;
   d =
1e6;
  
x = 19875000 ;
   y = 200000000;
  
format _all_ million. ;
   put (_all_)(=best.);
   put (_all_)(=million.);
   run;


a=49999 b=70000 c=100000 d=1000000 x=19875000 y=200000000

a=$0.0 b=$0.1 c=$0.1 d=$1.0 x=$19.9 y=$200.0

ChrisHemedinger
Community Manager

This thread and the BIGMONEY example prompted me to complete this blog post, which I had been mulling for a while:

Format your data like a social media superstar - The SAS Dummy

SAS formats are super powerful, and PICTURE formats can be immensely useful.  Enjoy!

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 7 replies
  • 11975 views
  • 3 likes
  • 6 in conversation