How to convert percents to real number?

[IgorR]

Hi,

My question has two parts:

1. I import data from excel and it comes to SAS in this format:

2008 | 1 | (18.13%) | (19.3%)
2009 | 1 |  25.50%  |  23.8%
2010 | 1 |   8.55%  |   7.0%
2011 | 1 | ( 6.42%) | ( 7.7%)

I need to convert the percents to real numbers format so it will look like this (precision does important!!!):

2008 | 1 | -0.1812793769191860| -0.1925832119518600
2009 | 1 |  0.2550005600450760|  0.2376731361391290
2010 | 1 |  0.0854571645604569|  0.0704705764896025
2011 | 1 | -0.0641682597261736| -0.0770890135366603

The Question - How to do this?

2. I will glad to get all real numbers formats i can use in format statement. I can't find them in net.

Thanks in advance.

[Reeza]

For the current variables in SAS, what is the type and format assigned?

[ballardw]

I seriously doubt that SAS placed any pipe characters between values.

How did you import those values?

Run Proc Contents on the data set and show us the result with the variable names, column positions, formats and informats.

[IgorR]

The proc content results:

The CONTENTS Procedure

Data Set Name : WORK.NET_YIELDS 
Observations : 40 
Member Type : DATA 
Variables : 4 
Engine : V9 
Indexes : 0 
Created : 08/09/2023 08:18:55 
Observation Length : 32 
Last Modified : 08/09/2023 08:18:55 
Deleted Observations : 0 
Protection : 
Compressed : NO 
Data Set Type :
Sorted : NO 
Label :   
Data Representation : WINDOWS_64 
Encoding : whebrew Hebrew (Windows) 

Engine/Host Dependent Information

Data Set Page Size : 65536 
Number of Data Set Pages : 1 
First Data Page : 1 
Max Obs per Page : 2039 
Obs in First Data Page : 40 
Number of Data Set Repairs : 0 
ExtendObsCounter : YES 
Filename : \net_yields.sas7bdat 
Release Created : 9.0401M7 
Host Created : 
Owner Name :  
File Size : 128KB 
File Size (bytes) : 131072 

Alphabetic List of Variables and Attributes
# Variable        Type  Len  Format   Label
1 CalYear         Num  8      BEST.    Cal year 
3 GrossReturn Num  8      10.4       Gross return 
2 Historic          Num 8       BEST.    Historic? 
4 NetReturn     Num  8      10.4       Net return 

[Kurt_Bremser]

This PROC CONTENTS output is very clearly not from the dataset in your initial post. Variable names and/or labels are different.

[Tom]

If you don't want the values to print like percents the REMOVE the attached format.

You don't say what the variables are named, let's call them PERCENT1 and PERCENT2.

So let's try to re-create your dataset from your "listing" of the data.

data have;
  infile cards dlm='|';
  input year x percent1 percent2 ;
  format percent1 percent2 percent9.2;
cards;
2008 | 1 | -0.1812793769191860| -0.1925832119518600
2009 | 1 |  0.2550005600450760|  0.2376731361391290
2010 | 1 |  0.0854571645604569|  0.0704705764896025
2011 | 1 | -0.0641682597261736| -0.0770890135366603
;

Now it we just print it the result will look like:

Obs    year    x     percent1     percent2

 1     2008    1    ( 18.13%)    ( 19.26%)
 2     2009    1      25.50%       23.77%
 3     2010    1       8.55%        7.05%
 4     2011    1    (  6.42%)    (  7.71%)

But if we print it WITHOUT the PERCENT format attached the variables

proc print data=have;
  format percent1 percent2 ;
run;

then we get:

Obs    year    x    percent1    percent2

 1     2008    1    -0.18128    -0.19258
 2     2009    1     0.25500     0.23767
 3     2010    1     0.08546     0.07047
 4     2011    1    -0.06417    -0.07709

because by default SAS will use the BEST12 format to display numbers.

If you want to see more decimal places then use a different display format.

[IgorR]

Hi,

I use this code to import the data from excel file. The file is not belong to me, so I can't change the data appearance within the file:

Proc Import Out = Net_Yields
Datafile = "\File.xlsx"
dbms = xlsx
replace;
Getnames = YES;
Sheet = "Gtee Vector";
Range = "S5:V45";
Run;

This code return the values as percent.

But to perform calculations on the values I need them to be in format of real numbers with all decimals. It's very important because i work with very large numbers and every decimal make sense.

So I need format that will keep the whole data.

Thank you.

[ballardw]

---

@IgorR wrote:

Hi,

I use this code to import the data from excel file. The file is not belong to me, so I can't change the data appearance within the file:

Proc Import Out = Net_Yields
Datafile = "\File.xlsx"
dbms = xlsx
replace;
Getnames = YES;
Sheet = "Gtee Vector";
Range = "S5:V45";
Run;

This code return the values as percent.

But to perform calculations on the values I need them to be in format of real numbers with all decimals. It's very important because i work with very large numbers and every decimal make sense.

So I need format that will keep the whole data.

Thank you.

---

Then you are doing something else. If this is from that Work.Net_yields shown in proc contents then none of the variables would show a % anywhere. BEST formats will fit the number of digits displayed into the length, 10.4 will display the numeric value with 4 decimal places.

# Variable        Type  Len  Format   Label
1 CalYear         Num  8      BEST.    Cal year 
3 GrossReturn Num  8      10.4       Gross return 
2 Historic          Num 8       BEST.    Historic? 
4 NetReturn     Num  8      10.4       Net return 

[IgorR]

The best I could get by "Format GrossReturn 10.5;" is five decimals, but this is not good enough for me. I need to get all 16 decimals. Is it possible at all?

[FreelanceReinh]

Hi @IgorR,

---

@IgorR wrote:

But to perform calculations on the values I need them to be in format of real numbers with all decimals. It's very important because i work with very large numbers and every decimal make sense.

So I need format that will keep the whole data.

---

Note that a SAS format does not change the value of a variable. So, if the calculations are performed in SAS, you can be sure that all decimals (more precisely: all binary digits of the internal representation) are used, regardless of the format attached or used for display.

Example (log) using one of the values from your initial post:

130   data _null_;
131   p=0.0854571645604569;
132   put (4*p)(best16. / best20.-l / +1 e21. / binary64.);
133   run;

0.08545716456046
0.08545716456045
  8.54571645604570E-02
0011111110110101111000001000010101001110111111101111110101001110

As you can see, in this case the E21. format reveals one additional decimal and shows that BEST16., unlike BEST20., rounded the previous decimal correctly. Yet, the "...70" is still rounded, compared to the original numeric literal in the assignment statement. A conversion of the binary digits of the internal binary representation (shown by the BINARY64. format) results in

0.0854571645604569052334653633806738071143627166748046875

confirming that also the last decimal (9) of the original value is present internally. Better still, the conversion of internal binary representations modified in the least significant bit (-/+ 1) shows that the internal accuracy goes slightly beyond that last decimal:

0.08545716456045689135567755556621705181896686553955078125
0.0854571645604569052334653633806738071143627166748046875
0.08545716456045691911125317119513056240975856781005859375

[Astounding · Accepted solution]

You will need to compromise a little if using base SAS software. SAS does not guarantee numeric precision beyond 15 significant digits.

Try applying the format 18.15. The width of 18 allows for a negative sign and a decimal point.