Convert double variables to float

[Francois Durant]

Hi,

I am currently "translating" a piece of SAS code to Stata. Each step, I verify that both my Stata code and the SAS code produce the same outcome, which is what I'm aiming for. When I import the code produced from the SAS code to a Stata dataset, I find some variable precision mismatches (float or double) that occasionally create large difference when I generate new variables (large dataset). I would like to get the 2 codes to produce the same outcome and I cannot modify the SAS code. Results are generated from the same initial dataset source (SAS format).

I was thinking about changing the precision of the variables produced by the SAS code to float so that I can check step by step that the 2 codes do the same thing (beside these few observations that will in reality mismatch but will be eliminated in the very last step through winsorization). How can I do that?

Alternatively, I could import the initial SAS data to Stata with double precision, but I could not find the option to do so in "import sas " in Stata. Is there a better way to solve the problem?

here is an example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey int fyear double(ebit1 ebit nopi1 nopi ceq1 ceq lt1 lt che1 che roic1 roic)
"001084" 2001 -2.127 -2.127  .006  .006 -4.699 -4.699 4.705 4.705  .006  .006                   .   -9386189195902306
"001084" 2002   .059   .059     0     0 -4.762 -4.762 4.775 4.775  .013  .013   66428094503714.81   74749855793191.17
"001084" 2003  -.029  -.029     0     0 -4.942 -4.942 4.944 4.944  .002  .002                   .  131632770211017.97
"001084" 2004  -.029  -.029     0     0 -4.795 -4.795 4.797 4.797  .002  .002                   .  131632770211017.97
"001084" 2005  -.014  -.014     0     0 -4.712 -4.712 4.715 4.715  .003  .003                   . -123212985225159.22
"001084" 2006   -.05   -.05     0     0 -4.838 -4.838  4.84  4.84  .002  .002                   .  226953052087962.03
"001084" 2014  -.816  -.816  .199  .199 -4.604 -4.604 4.632 4.632  .028  .028                   .  2417800262760226.5
"001084" 2015  -.769  -.769 -.762 -.762 -5.142 -5.142 5.168 5.168  .026  .026                   .   35396712860736.56
"001084" 2016  -.829  -.829  .006  .006 -4.779 -4.779 4.872 4.872  .093  .093                   .   30084045510834912
"001084" 2018 -2.893 -2.893 4.692 4.692  -.595  -.595 4.441 4.441 3.846 3.846   34159803173605212                   .
"001718" 1995  -.565  -.565   .32   .32  -.253  -.253   .31   .31  .057  .057                   .  127541941447132448
"002831" 1999      0      0     0     0  -.189  -.189   .19   .19  .001  .001                   .                   0
"003515" 2003  -.384  -.384     0     0 -7.891 -7.891 8.339 8.339  .448  .448                   .  -988218432520154.6
"004173" 2001  -.083  -.083     0     0  -.024  -.024  .037  .037  .013  .013                   .   47846242441184152
"004359" 1997  -.014  -.014     0     0  -.009  -.009   .01   .01  .001  .001   -8070450532247929  -16140901064495858
"004562" 1988  -.061  -.061     0     0  -.321  -.321  .326  .326  .005  .005                   .  -14065642356203532
"005045" 1999  -.027  -.027     0     0   .029   .029  .035  .035  .064  .064   -7782220156096217                   .
"005045" 2001  -.027  -.027     0     0   .009   .009  .023  .023  .032  .032   15564440312192434                   .
"005215" 2007  -.041  -.041     0     0   -.04   -.04  .042  .042  .002  .002                   .  -23634890844440364
"005215" 2008  -.059  -.059     0     0  -.099  -.099  .104  .104  .005  .005    4251398048237748    6183851706527633
"005215" 2009  -.222  -.222     0     0  -.237  -.237  .242  .242  .005  .005                   .  -51189714804544008
"005215" 2011  -.156  -.156     0     0  -.724  -.724  .729  .729  .005  .005                   .  -35971150943733624
"006683" 2013 -1.574 -1.574     0     0 -1.658 -1.658 1.667 1.667  .009  .009   -7088665813481161  -13149988755443314
"006741" 2005  -.093  -.093     0     0  -.132  -.132  .139  .139  .007  .007                   .  -15317385704062396
"006741" 2006  -.063  -.063  .002  .002  -.207  -.207   .21   .21  .003  .003                   .  -24979965933148352
"007279" 1977  8.468  8.468  .091  .091     .1     .1 1.183 1.183 1.283 1.283   60362646525572240   37726654078482648
"007279" 1978 19.267 19.267     0     0    .23    .23  5.75  5.75  5.98  5.98  -46277788810958584                   .
"007279" 1979 18.391 18.391  .013  .013    .36    .36 4.533 4.533 4.893 4.893   33106861580725984   20691788487953740
"007279" 1980 11.318 11.318  .029  .029    .36    .36 2.762 2.762 3.122 3.122  101682272386771056                   .
"007279" 1981  19.45  19.45  .153  .153    .36    .36 4.563 4.563 4.923 4.923  -57937308006245640                   .
"007279" 1982  6.353  6.353  .096  .096    .36    .36  .958  .958 1.318 1.318  -56358045736914384                   .
"007279" 1983  8.283  8.283  .061  .061  1.781  1.781 2.624 2.624 4.405 4.405  -37028596136240216                   .
"007939" 1985  -.691  -.691 -.019 -.019 -1.564 -1.564 1.568 1.568  .004  .004                   . -193690812773950272
"007939" 1986  -.205  -.205 -.004 -.004  -1.59  -1.59 1.591 1.591  .001  .001                   .  1824702538787214.3
"007939" 1987  -.216  -.216  .001  .001 -1.786 -1.786 1.789 1.789  .003  .003                   .  2001471731997486.3
"007959" 1992 12.783 12.783  .088  .088   .015   .015 2.986 2.986 3.001 3.001   39772658970065008   28586598634734224
"007959" 1993 10.204 10.204  .045  .045   .044   .044 2.689 2.689 2.733 2.733 -244011032610436640                   .
"007959" 1994  8.731  8.731  .046  .046   .019   .019 1.829 1.829 1.848 1.848  -67656238293989640  -39113762763712760
"007959" 1995 11.865 11.865  .077  .077   .041   .041  2.91  2.91 2.951 2.951  154439076094380832                   .
"007959" 1996  9.014  9.014  .072  .072    .13    .13 2.348 2.348 2.478 2.478  -26847458578631316  -20135593933973488
"007959" 1997 12.978 12.978  .092  .092   .067   .067 2.957 2.957 3.024 3.024  -77377846397728272                   .
"007959" 1998 13.295 13.295  .102  .102    .07    .07 2.696 2.696 2.766 2.766   79221319845198608                   .
"007959" 1999 10.098 10.098   .07   .07   .058   .058 2.261 2.261 2.319 2.319   57807484240987312                   .
"007959" 2000 13.572 13.572  .096  .096   .014   .014 2.933 2.933 2.947 2.947  -57543593318821736  -30345254289222400
"007959" 2001  21.77  21.77  .137  .137   .059   .059 5.332 5.332 5.391 5.391 -129901827651874576                   .
"007959" 2002 16.825 16.825  .061  .061   .065   .065 3.394 3.394 3.459 3.459  301993376612955968                   .
"007959" 2003 17.367 17.367  .031  .031   .045   .045 4.019 4.019 4.064 4.064  249838090048303776                   .
"007959" 2004 14.286 14.286  .022  .022   .066   .066 2.948 2.948 3.014 3.014   85652460113083664                   .
"007959" 2005 20.164 20.164  .059  .059   .064   .064 3.856 3.856  3.92  3.92 -362179482033135296                   .
"007959" 2006 30.095 30.095  .164  .164   .036   .036 7.168 7.168 7.204 7.204   73110367699973584                   .
"007959" 2007 26.532 26.532  .208  .208   .031   .031 5.882 5.882 5.913 5.913  -45162954891771792  -29638189147725236
"007959" 2008 33.569 33.569  .096  .096   .058   .058 9.467 9.467 9.525 9.525  192958707618524992                   .
"007959" 2009 27.688 27.688  .011  .011   .094   .094 3.492 3.492 3.586 3.586  199433803018773152                   .
"007959" 2010 18.713 18.713  .007  .007   .065   .065 5.147 5.147 5.212 5.212   37441926502041112   21061083657398124
"007959" 2011  24.17  24.17  .026  .026    .09    .09 5.882 5.882 5.972 5.972  -32217750934291336  -27183727350808316
"007959" 2012  22.57  22.57   .04   .04   .091   .091 4.687 4.687 4.778 4.778   32469151873490328   25366524901164320
"007959" 2013  20.61  20.61  .025  .025   .047   .047 4.871 4.871 4.918 4.918   70633598727178408                   .
"007959" 2014 18.026 18.026  .019  .019   .171   .171 3.584 3.584 3.755 3.755   92681506845783456                   .
"007959" 2015 11.572 11.572  .009  .009   .079   .079 2.114 2.114 2.193 2.193  -64092458450812360                   .
"007959" 2016  6.137  6.137  .004  .004   .062   .062 1.103 1.103 1.165 1.165 -110482306058653008                   .
"007959" 2017  7.022  7.022  .004  .004   .104   .104 2.022 2.022 2.126 2.126  -72242884994025472                   .
"007959" 2018  6.403  6.403  .005  .005   .079   .079 1.378 1.378 1.457 1.457  -35463422050358684  -28814030415916432
"007959" 2019  7.564  7.564  .014  .014    .12    .12 1.471 1.471 1.591 1.591   68004354373294488   34002177186647244
"008381" 2012  -.005  -.005     0     0  -.011  -.011  .072  .072  .061  .061  1441151880758558.8                   .
"008753" 1983  -.039  -.039     0     0  -.259  -.259  .261  .261  .002  .002                   .  -22481969339833516
"009048" 2008  -.108  -.108 -.002 -.002  -.001  -.001  .044  .044  .043  .043 -122209679488325776                   .
"009048" 2009  -.023  -.023     0     0   .015   .015  .006  .006  .021  .021   13258597302978740    6629298651489370
"009048" 2015   -.03   -.03     0     0  -.054  -.054  .066  .066  .012  .012   -4323455642275676   -8646911284551352
"009048" 2017   -.02   -.02     0     0  -.113  -.113  .121  .121  .008  .008  1441151880758558.8  2882303761517117.5
"009048" 2018  -.023  -.023     0     0  -.146  -.146  .154  .154  .008  .008                   .   -3314649325744685
"009551" 2005  -.171  -.171     0     0  -.071  -.071  .074  .074  .003  .003                   .  -65716525762590280
"010019" 2013  -.791  -.791     0     0 -2.062 -2.062 2.065 2.065  .003  .003                   .   -6961533665221496
"010278" 2016  -.695  -.695  .695  .695   .326   .326 1.125 1.125 1.451 1.451   25040013928179956                   .
"010564" 2012   .787   .787     0     0     .7     .7  .174  .174  .874  .874                   .   -7088665813481161
"010564" 2016   .088   .088     0     0   .488   .488  .036  .036  .524  .524 -1585267068834414.5                   .
"011409" 1997   .423   .423  .208  .208 -4.802 -4.802 5.418 5.418  .616  .616  242068479971164.16   387309567953862.6
"011739" 2015  -.056  -.056     0     0  -.028  -.028  .031  .031  .003  .003                   .   64563604257983432
"011836" 1997  -.039  -.039     0     0  -.041  -.041  .042  .042  .001  .001                   .  -44963938679667032
"011897" 2013  -.107  -.107     0     0  -.227  -.227  .247  .247   .02   .02                   .   10280216749411052
"011905" 2014 -1.852 -1.852     0     0 -2.071 -2.071 2.078 2.078  .007  .007  4170333254945079.5    5663688664540798
"011916" 2014  -.073  -.073     0     0  -.046  -.046  .047  .047  .001  .001                   .  -84163269836299824
"012109" 1997  -.011  -.011     0     0  -.027  -.027   .03   .03  .003  .003                   .   12682136550675316
"012109" 1998  -.005  -.005     0     0  -.035  -.035  .038  .038  .003  .003   720575940379279.4    1152921504606847
"012109" 1999  -.009  -.009     0     0  -.047  -.047  .049  .049  .002  .002                   .   -5188146770730811
"012109" 2000  -.097  -.097     0     0  -.024  -.024  .027  .027  .003  .003                   .  111833385946864160
"012132" 2000  -.012  -.012     0     0  -.025  -.025  .026  .026  .001  .001    3458764513820541    4611686018427388
"012134" 2000  -.229  -.229     0     0  -.017  -.017  .018  .018  .001  .001   66004756138741992   88006341518322656
"012573" 2003  -.182  -.182     0     0  -.605  -.605  .614  .614  .009  .009                   .  -20983171383844616
"012573" 2010  -.752  -.752     0     0 -1.845 -1.845 1.846 1.846  .001  .001   -3386706919782613   -6720906755537589
"012573" 2012  -.342  -.342     0     0 -2.281 -2.281 2.282 2.282  .001  .001                   .  3104717752563320.5
"012595" 2002 -6.313 -6.313  .004  .004 -4.844 -4.844 4.845 4.845  .001  .001    7112309711524855   11397504138656418
"012724" 1996  -.382  -.382  .002  .002  -.554  -.554  .556  .556  .002  .002                   . -221360928884514624
"012724" 1998  -.097  -.097     0     0  -.046  -.046  .048  .048  .002  .002                   .  -55916692973432080
"012724" 1999  -.026  -.026     0     0  -.035  -.035  .036  .036  .001  .001  3746994889972252.5    4282279874254003
"012741" 1998   .004   .004  .002  .002  -.187  -.187  .209  .209  .022  .022                   . -288230376151711.75
"012744" 2007  -.111  -.111     0     0  -.529  -.529  .533  .533  .004  .004                   .  -31993571752840004
"012807" 1998   -.25   -.25     0     0   -.03   -.03  .036  .036  .006  .006                   .  144115188075855872
"012807" 2002   -.43   -.43     0     0  -.536  -.536  .537  .537  .001  .001                   . -495756246980944192
"012807" 2006  -.995  -.995     0     0  -.673  -.673  .704  .704  .031  .031    8962163258467287   11949551011289716
"012807" 2017 -1.622 -1.622  .091  .091 -4.157 -4.157 4.454 4.454  .297  .297                   .    6171732929348528
end

gen roic=(ebit-nopi)/(ceq+lt-che)
gen roic1=(ebit1-nopi1)/(ceq1+lt1-che1)

. sum roic1 roic

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
       roic1 |    315,074    1.64e+13    6.90e+15  -2.07e+18   1.79e+18
        roic |    315,350   -1.17e+14    1.72e+16  -6.14e+18   1.27e+18

[Joseph Coveney]

I was thinking about changing the precision of the variables produced by the SAS code to float so that I can check step by step that the 2 codes do the same thing (beside these few observations that will in reality mismatch but will be eliminated in the very last step through winsorization). How can I do that?

I'm not really following what you're trying to do, and I'm not sure how this is going to help you, but you can truncate precision of a SAS variable for storage in a dataset file using SAS's LENGTH statement.

Alternatively, I could import the initial SAS data to Stata with double precision, but I could not find the option to do so in "import sas " in Stata. Is there a better way to solve the problem?

Are you saying that Stata's import sas converts SAS datasets' double-precision floating point variables to single-precision floating point? I don't think that that's the case. See below.

.ÿ
.ÿversionÿ16.1

.ÿ
.ÿclearÿ*

.ÿ
.ÿlocalÿline_sizeÿ`c(linesize)'

.ÿsetÿlinesizeÿ79

.ÿ
.ÿimportÿsasÿusingÿ///
>ÿÿÿÿÿ"http://www.principlesofeconometrics.com/sas/cocaine.sas7bdat",ÿ///
>ÿÿÿÿÿcase(preserve)
(4ÿvars,ÿ56ÿobs)

.ÿdescribe

Containsÿdata
ÿÿobs:ÿÿÿÿÿÿÿÿÿÿÿÿ56ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿCOCAINE
ÿvars:ÿÿÿÿÿÿÿÿÿÿÿÿÿ4ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
-------------------------------------------------------------------------------
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿstorageÿÿÿdisplayÿÿÿÿvalue
variableÿnameÿÿÿtypeÿÿÿÿformatÿÿÿÿÿlabelÿÿÿÿÿÿvariableÿlabel
-------------------------------------------------------------------------------
PRICEÿÿÿÿÿÿÿÿÿÿÿdoubleÿÿ%10.0gÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿpriceÿperÿgramÿinÿdollarsÿforÿa
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿcocaineÿsale
QUANTÿÿÿÿÿÿÿÿÿÿÿdoubleÿÿ%10.0gÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿnumberÿofÿgramsÿofÿcocaineÿinÿa
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿgivenÿsale
QUALÿÿÿÿÿÿÿÿÿÿÿÿdoubleÿÿ%10.0gÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿqualityÿofÿtheÿcocaineÿexpressed
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿasÿpercentageÿpurity
TRENDÿÿÿÿÿÿÿÿÿÿÿbyteÿÿÿÿ%10.0gÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿaÿtimeÿvariableÿwithÿ1984ÿ=ÿ1ÿup
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿtoÿ1991ÿ=ÿ8
-------------------------------------------------------------------------------
Sortedÿby:ÿ
ÿÿÿÿÿNote:ÿDatasetÿhasÿchangedÿsinceÿlastÿsaved.

.ÿ
.ÿsetÿlinesizeÿ`line_size'

.ÿ
.ÿexit

endÿofÿdo-file


.

[Francois Durant]

I do not understand how to use the SAS's LENGTH statement you mentioned. Could you provide more details?

I'm not really an expert in SAS so I'm not entirely sure what's going on. That said, when I use the merge the Stata generated dataset (varaibles ending by 1) and the SAS generated dataset (same varaibles without 1), I find that "roic" produced with SAS is different than "roic1" produced with Stata.

I have also recalculated roic, names "roic2" in the screenshot enclosed, using the SAS imported variables (without 1) and I find exactly the same results as the Stata generated variables as you can see on the screenshot. Any suggestion?

en roic2 = (ebit-nopi)/(ceq+lt-che)

[Bjarte Aagnes]

If SAS used 8-byte float, why not save your Stata variables as double?

set type double

see

help generate
help datatypes
help dta

and https://blog.stata.com/2012/04/02/th...-to-precision/

[Joseph Coveney]

I do not understand how to use the SAS's LENGTH statement you mentioned. Could you provide more details?

Google sas length-statement and you can see SAS Institute's official documentation on its use.

. . .when I use the merge the Stata generated dataset (varaibles ending by 1) and the SAS generated dataset (same varaibles without 1), I find that "roic" produced with SAS is different than "roic1" produced with Stata.

Again, I have no idea what you're trying to do, how you got the roic variable in SAS, what your -merge- entailed, or why roic and roic1 not only don't match quantitatively, but even more why most of the variables are missing for one and not for the other.

The only suggestion I could offer—and it's one that I wish StataCorp would institute as a default (I think that Stata stands alone among statistical analysis software packages in this respect)—is to

set type double, permanently

and see whether that helps.

[Francois Durant]

roic is generated from SAS. Once done, I convert the SAS dataset to a Stata dataset using Stata (import sas ...)
roic1 is generated from Stata using the same initial data source.
I merge both dataset by date and company (100% merge success). I am doing this because I want to catch potential mistakes I could make in my Stata code since I am "translating" a SAS code.

As mentioned above, here is how to generate roic and roic1:

 
 gen roic = (ebit-nopi)/(ceq+lt-che)

If the denominator is exactly 0, it generates missing roic. If this denominator is very small in absolute value, it generates very large roic in absolute value. Slight difference in the variables "ebit nopi ceq lt che" induce this difference, which I believe come from different double/float format. Does that make more sense now Joseph Coveney ? Thanks for your help in advance.

[William Lisowski]

Your calculations are meaningless for those observations where the denominator is zero or very small in absolute value. I am not surprised that Stata and SAS would get different results under those circumstances, especially if some of the values were stored in SAS as length 4 (float) and others as length 8 (double). Trying to reproduce meaningless results is not a useful exercise.

You should concern yourself with how the results compare for meaningful calculations. And I hope the SAS code you are trying to reproduce eventually discards or ignores the meaninglessly large values of roic.

[Francois Durant]

I'm trying to adapt someone else SAS code to Stata. I need to get exactly the same results steps by steps to make sure I'm did not make a mistake in my Stata code and also to understand the best way I am going to deal with these problematic observations / outliers at a large scale since there are more than 400 variables like roic (winsorisation? which level? delete obs? set as missing etc).

I'm trying to conduct precision work on a dataset I'm going to use for years for research purpose. This is why this exercise and my request is meaningful in my particular case.

[William Lisowski]

Your exercise is meaningful to you, but your approach is misguided.

Clearly these results from division by zero or near-zero are eventually going to have to be discarded from the analysis. So why try to exactly reproduce results you are going to discard? Start by seeing if you are successfully reproducing results you are not going to discard.

Drop the observations for which (ceq+lt-che)> 0.01, for example. How do the remaining observations compare? If they match reasonably closely, you're code is on the right track. Now reduce the cutoff to 0.001 and repeat the comparison. And so forth, until you reach the threshold where nonsense predominates, perhaps at 0.00000000001.

Look at your first observation in post #1. The roic is

-9386189195902306 = -9.3 * 1015

and the numerator of the roic calculation is

ebit - nopi = -2.127 - .006 = -2.133

and from that we find that the denominator that yielded that value for roic is on the order of

2.2 * 10-16

Really? How is that different from zero in any significant way, when you're computing the denominator by combining three numbers each larger than 10^-3. That is what I mean when I say you are comparing meaningless numbers.

[Francois Durant]

William: Bottom line, I am trying to get the 2 dataset in the same float/double format so that I can figure out quickly if I'm making an error in my Stata code. Is there a quick fix I can apply to my problem here?

[William Lisowski]

No.

[Francois Durant]

I am trying to assess the downside of setting permanently type as double by default.

What would be the approximate size/volume differential of a dataset of 10 variables float of, say, 100Mo saved as *.dta vs the same exact dataset with 10 variables saved as double? I'm seeking for an approximate quantified rule a thumb (as opposed to a "it depends" type of answer, although I'm aware it depends on many parameters). Thanks.

[Bjarte Aagnes]

https://www.stata.com/support/faqs/data-management/approximating-dataset-size/

help data_types

                                                         Closest to
    Storage                                              0 without
    type                 Minimum              Maximum    being 0     bytes
    ----------------------------------------------------------------------
    byte                    -127                  100    +/-1          1
    int                  -32,767               32,740    +/-1          2
    long          -2,147,483,647        2,147,483,620    +/-1          4
    float   -1.70141173319*10^38  1.70141173319*10^38    +/-10^-38     4
    double  -8.9884656743*10^307  8.9884656743*10^307    +/-10^-323    8
    ----------------------------------------------------------------------