Re: [R] Reading sas7bdat files directly
Just wondering if the proposed loop for the metadata on this conversion was implemented by any chance...before I go trying the Long routine... -- View this message in context: http://r.789695.n4.nabble.com/Reading-sas7bdat-files-directly-tp1469515p3060319.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
The dsread output is little-endian, as that's the native format for floats on the Wintel platform. The byte order should stay the same if converting directly to a float, using a data structure like (C/C++): union { char bytes[8]; double value; } If reading the values with a SAS HEX informat, the bytes will need to be reversed. It's obviously trivial for me to add an endian-ness option, I'll do that later Chris. On 02/03/2010 02:06, Roger DeAngelis(xlr82sas) wrote: Hi, It looks like we may need to swap bytes(little endian to big endian). I will look into it tonight. As a side note, SAS reserves 28 floats for missing values. It should be easy to convert these to NaN on input to R. You can test this in SAS by converting the 16 char floats to ieee8. in SAS and doing a put. The result will be A, B...Z, . and _. SAS code that produced the listing is below. Here are the floats that map to the 28 missing values in SAS A FD00 B FC00 C FB00 D FA00 E F900 F F800 G F700 H F600 I F500 J F400 K F300 L F200 M F100 N F000 O EF00 P EE00 Q ED00 R EC00 S EB00 T EA00 U E900 V E800 W E700 X E600 Y E500 Z E400 _ FF00 . FE00 data mis; retain A .A B .B C .C D .D E .E F .F G .G H .H I .I J .J K .K L .L M .M N .N O .O P .P Q .Q R .R S .S T .T U .U V .V W .W X .X Y .Y Z .Z _ ._ DOT .; array mis[28] A B C D E F G H I J K L M N O P Q R S T U V W X Y Z _ DOT; do idx=1 to 28; hex=put(mis[idx],ieee8.); xeh=put(hex,hex16.); put @1 mis[idx] @6 xeh; end; [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
dsread (http://www.oview.co.uk/dsread) was updated yesterday, to include various new features as suggested here and elsewhere. Of particular interest might be: - you can now use the /c and /v options together to get dataset contents in CSV format for easier importing; - there is now a /l option for lossless representation of numerics in the output. Numerics will appear as (eg) '0xf03f' giving the exact hex value of each of the eight bytes making up the IEEE float value. Please continue to report bugs or feature requests here or on the above web page. Thanks, Chris. -- View this message in context: http://n4.nabble.com/Reading-sas7bdat-files-directly-tp1469515p1574340.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
Hi All, The hack below might help R users get going with Chris's DSREAD. I have not had a chance to look at Monday's version of DSREAD, can't wait. Note Duncan Murdoch was most gracious to supply me with a R function to translate floats in 16 char hex to R floats. Your utility solves the 200 byte, 8 char name and potential precision errors with other methods of transfering SAS datasets to perl and R. Thanks. Importing SAS datasets(sas7bdat) into R (32 bit windows 2000, 32 bit SAS 9.2 and 32 bit R version 2.9.0 (2009-04-17) Here is what I want to accomplish, the double floats below show data from SAS to R. They are exactly the same in R and SAS memory, bit for bit. R Internal SAS Internal 16 Byte Float 16 byte Float 3FFB 3FFB 4002AAAB 4002AAAB 400D 400D 3FF6 3FF6 3FFD 3FFD 4001999A 4001999A 4004CCCD 4004CCCD 3FF4924924924925 3FF4924924924925 3FF9249249249249 3FF9249249249249 3FFDB6DB6DB6DB6E 3FFDB6DB6DB6DB6E 4001249249249249 4001249249249249 3FF2E8BA2E8BA2E9 3FF2E8BA2E8BA2E9 3FF5D1745D1745D1 3FF5D1745D1745D1 3FF8BA2E8BA2E8BA 3FF8BA2E8BA2E8BA 3FFBA2E8BA2E8BA3 3FFBA2E8BA2E8BA3 3FF2762762762762 3FF2762762762762 3FF4EC4EC4EC4EC5 3FF4EC4EC4EC4EC5 3FF7627627627627 3FF7627627627627 3FF9D89D89D89D8A 3FF9D89D89D89D8A 1.7976931348623E 1.7976931348623E 0010 0010 I don't believe this high accuracy transfer is possible with any other method except ODBC, but SAS ODBC is unsatisfactory for me. If you use CSV with the maximum assured decimal precision(15 significant digits?). The CSV decimal numbers will only approximate the double floats. I consider the Csv to be corrupt if the relative of absolute difference using the decimal Csv numbers and the memory floats is greater than 10^-12. There are two sources of error first the SAS floats are decimally rounded and converted to decimal then the rounded decimal approximations are converted into R floats. Status of R InternalCSV Csv 16 Byte Float Csv corrupt 3FFB 1.6710^-12 different Csv corrupt 4002AAAB 2.33 Csv corrupt 400D 3.67 Csv OK 3FF6 1.4 Csv OK 3FFD 1.8 Csv OK 4001999A 2.2 Csv OK 4004CCCD 2.6 Csv corrupt 3FF4924924924925 1.28571428571429 Csv corrupt 3FF9249249249249 1.57142857142857 Csv corrupt 3FFDB6DB6DB6DB6E 1.85714285714286 Csv corrupt 4001249249249249 2.14285714285714 Csv corrupt 3FF2E8BA2E8BA2E9 1.18181818181818 Csv corrupt 3FF5D1745D1745D1 1.36363636363636 Csv corrupt 3FF8BA2E8BA2E8BA 1.54545454545455 Csv corrupt 3FFBA2E8BA2E8BA3 1.72727272727273 Csv corrupt 3FF2762762762762 1.15384615384615 Csv corrupt 3FF4EC4EC4EC4EC5 1.30769230769231 Csv corrupt 3FF7627627627627 1.46153846153846 Csv corrupt 3FF9D89D89D89D8A 1.61538461538462 Csv corrupt 1.7976931348623E 1.7976931348623E+308 Csv corrupt 0010 2.2250738585072E-308 Bacground 1. Provide absolutely loss less transfer of character(max 32756 bytes per character variable) and numeric data from SAS to R Since SAS has only two datatypes so this code should be exhaustive. 2. This code is useful because: a. The SAS ODBC driver requires the user to not only have SAS but the user must bring up a SAS session and the session has to be closed manually. (SAS issue not a foreign issue) b. The foreign package also requires interaction with SAS. (SAS issue) c. SASxport only supports 8 character SAS names and a max of 200 byte character values. (This is a SAS issue not a SASxport issue) d. SASxport creates floating point doubles that have an 8 bit exponent and 56 bit mantissa while IEEE is 11 bit exponent and 53 bit mantissa (sometimes defined slightly differently depending of where you consider the sign bits). This results is the loss of some very small and very large numbers. ( SAS issue not a SASxport issue) 3. How this code overcomes the issues above for import only. You need the dsread exec in the previous mesage. Also the input SAS dataset must have 16 byte character representations for the floats. I am working with the developer to see what we can do about this.. He will make it an option on the invocation to do the hex conversion for numerics. Here is the R code run inside a SAS datastep. Actually I can interact with the output of the R code in the same dataqstep. It is also possible to run perl, SAS procs and other SAS languages in the same datastep. Note the input pipe, no physical CSV file is produced). If there is interest I can provide the code that executes R. data
Re: [R] Reading sas7bdat files directly
-Original Message- From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org] On Behalf Of Roger DeAngelis(xlr82sas) Sent: Monday, March 01, 2010 4:38 PM To: r-help@r-project.org Subject: Re: [R] Reading sas7bdat files directly Hi All, The hack below might help R users get going with Chris's DSREAD. I have not had a chance to look at Monday's version of DSREAD, can't wait. Note Duncan Murdoch was most gracious to supply me with a R function to translate floats in 16 char hex to R floats. Your utility solves the 200 byte, 8 char name and potential precision errors with other methods of transfering SAS datasets to perl and R. Thanks. Importing SAS datasets(sas7bdat) into R (32 bit windows 2000, 32 bit SAS 9.2 and 32 bit R version 2.9.0 (2009-04-17) Here is what I want to accomplish, the double floats below show data from SAS to R. They are exactly the same in R and SAS memory, bit for bit. R Internal SAS Internal 16 Byte Float 16 byte Float 3FFB 3FFB 4002AAAB 4002AAAB 400D 400D 3FF6 3FF6 3FFD 3FFD 4001999A 4001999A 4004CCCD 4004CCCD 3FF4924924924925 3FF4924924924925 3FF9249249249249 3FF9249249249249 3FFDB6DB6DB6DB6E 3FFDB6DB6DB6DB6E 4001249249249249 4001249249249249 3FF2E8BA2E8BA2E9 3FF2E8BA2E8BA2E9 3FF5D1745D1745D1 3FF5D1745D1745D1 3FF8BA2E8BA2E8BA 3FF8BA2E8BA2E8BA 3FFBA2E8BA2E8BA3 3FFBA2E8BA2E8BA3 3FF2762762762762 3FF2762762762762 3FF4EC4EC4EC4EC5 3FF4EC4EC4EC4EC5 3FF7627627627627 3FF7627627627627 3FF9D89D89D89D8A 3FF9D89D89D89D8A 1.7976931348623E 1.7976931348623E 0010 0010 I don't believe this high accuracy transfer is possible with any other method except ODBC, but SAS ODBC is unsatisfactory for me. If you use CSV with the maximum assured decimal precision(15 significant digits?). The CSV decimal numbers will only approximate the double floats. I consider the Csv to be corrupt if the relative of absolute difference using the decimal Csv numbers and the memory floats is greater than 10^-12. There are two sources of error first the SAS floats are decimally rounded and converted to decimal then the rounded decimal approximations are converted into R floats. Status of R InternalCSV Csv 16 Byte Float Csv corrupt 3FFB 1.6710^-12 different Csv corrupt 4002AAAB 2.33 Csv corrupt 400D 3.67 Csv OK 3FF6 1.4 Csv OK 3FFD 1.8 Csv OK 4001999A 2.2 Csv OK 4004CCCD 2.6 Csv corrupt 3FF4924924924925 1.28571428571429 Csv corrupt 3FF9249249249249 1.57142857142857 Csv corrupt 3FFDB6DB6DB6DB6E 1.85714285714286 Csv corrupt 4001249249249249 2.14285714285714 Csv corrupt 3FF2E8BA2E8BA2E9 1.18181818181818 Csv corrupt 3FF5D1745D1745D1 1.36363636363636 Csv corrupt 3FF8BA2E8BA2E8BA 1.54545454545455 Csv corrupt 3FFBA2E8BA2E8BA3 1.72727272727273 Csv corrupt 3FF2762762762762 1.15384615384615 Csv corrupt 3FF4EC4EC4EC4EC5 1.30769230769231 Csv corrupt 3FF7627627627627 1.46153846153846 Csv corrupt 3FF9D89D89D89D8A 1.61538461538462 Csv corrupt 1.7976931348623E 1.7976931348623E+308 Csv corrupt 0010 2.2250738585072E-308 Bacground 1. Provide absolutely loss less transfer of character(max 32756 bytes per character variable) and numeric data from SAS to R Since SAS has only two datatypes so this code should be exhaustive. 2. This code is useful because: a. The SAS ODBC driver requires the user to not only have SAS but the user must bring up a SAS session and the session has to be closed manually. (SAS issue not a foreign issue) b. The foreign package also requires interaction with SAS. (SAS issue) c. SASxport only supports 8 character SAS names and a max of 200 byte character values. (This is a SAS issue not a SASxport issue) d. SASxport creates floating point doubles that have an 8 bit exponent and 56 bit mantissa while IEEE is 11 bit exponent and 53 bit mantissa (sometimes defined slightly differently depending of where you consider the sign bits). This results is the loss of some very small and very large numbers. ( SAS issue not a SASxport issue) 3. How this code overcomes the issues above for import only. You need the dsread exec in the previous mesage. Also the input SAS dataset must have 16 byte character representations for the floats. I am working with the developer to see what we can do about this.. He will make it an option on the invocation to do the hex conversion for numerics. Here is the R code run inside a SAS datastep. Actually
Re: [R] Reading sas7bdat files directly
Hi, It looks like we may need to swap bytes(little endian to big endian). I will look into it tonight. As a side note, SAS reserves 28 floats for missing values. It should be easy to convert these to NaN on input to R. You can test this in SAS by converting the 16 char floats to ieee8. in SAS and doing a put. The result will be A, B...Z, . and _. SAS code that produced the listing is below. Here are the floats that map to the 28 missing values in SAS A FD00 B FC00 C FB00 D FA00 E F900 F F800 G F700 H F600 I F500 J F400 K F300 L F200 M F100 N F000 O EF00 P EE00 Q ED00 R EC00 S EB00 T EA00 U E900 V E800 W E700 X E600 Y E500 Z E400 _ FF00 . FE00 data mis; retain A .A B .B C .C D .D E .E F .F G .G H .H I .I J .J K .K L .L M .M N .N O .O P .P Q .Q R .R S .S T .T U .U V .V W .W X .X Y .Y Z .Z _ ._ DOT .; array mis[28] A B C D E F G H I J K L M N O P Q R S T U V W X Y Z _ DOT; do idx=1 to 28; hex=put(mis[idx],ieee8.); xeh=put(hex,hex16.); put @1 mis[idx] @6 xeh; end; -- View this message in context: http://n4.nabble.com/Reading-sas7bdat-files-directly-tp1469515p1574506.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
I suppose a link would have added usefulness: http://www.oview.co.uk/dsread Chris. -- View this message in context: http://n4.nabble.com/Reading-sas7bdat-files-directly-tp1469515p1567256.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
Chris Long wrote: I suppose a link would have added usefulness: http://www.oview.co.uk/dsread Chris. As dsread seems to work perfectly under wine on Ubuntu linux (and quite quickly), it could be quite valuable to many of us. Thanks for posting this and for developing dsread! If time allows I'll write a function to add to the Hmisc package that runs dsread to output the SAS dataset metadata, then runs dsread again to read the data, adding back metadata such as variable labels. Frank -- Frank E Harrell Jr Professor and ChairmanSchool of Medicine Department of Biostatistics Vanderbilt University __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
No problem, Frank, I'm glad that you think it will be useful. I will be making changes to dsread in the coming weeks so you may want to hold off with your helper function in case my changes break it (the formatting of the variable metadata listing may well change). Re: the metadata, feel free to suggest an alternate output format that would make this a cleaner process. For example, I have in mind to allow the -c (contents) and -v (CSV) flags together, in which case the dataset metadata will be output in CSV format. Any other suggestions or comments are welcomed. Chris. -- View this message in context: http://n4.nabble.com/Reading-sas7bdat-files-directly-tp1469515p1567504.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
Chris Long wrote: No problem, Frank, I'm glad that you think it will be useful. I will be making changes to dsread in the coming weeks so you may want to hold off with your helper function in case my changes break it (the formatting of the variable metadata listing may well change). Re: the metadata, feel free to suggest an alternate output format that would make this a cleaner process. For example, I have in mind to allow the -c (contents) and -v (CSV) flags together, in which case the dataset metadata will be output in CSV format. Any other suggestions or comments are welcomed. Chris. Thanks very much for your note Chris. Having an option to output the metadata in a standard csv format as well as the nice table format you provide already (either one or the other) will be good. If you allow flags (one could be -o) to specify the names of 2 different output files when STDOUT is not being used, that will expedite things. As sas7bdat files are unbelievably inefficient storage-wise (I'm looking at an example where the file is 525K and the bzip2'd version is 29K) it will be great if you can handled compressed sas7bdat files too. Thanks for the excellent work, Frank -- Frank E Harrell Jr Professor and ChairmanSchool of Medicine Department of Biostatistics Vanderbilt University __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
If you don't have SAS and still need to read or write sas7bdat files: there is the World Programming System (WPS) (commercial software). http://www.teamwpc.co.uk/home/ -- View this message in context: http://n4.nabble.com/Reading-sas7bdat-files-directly-tp1469515p1557807.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
Hi Alex, Perhaps the read.ssd function in the foreign package might do what you want. See [1] for details. HTH, Jorge [1] http://cran.r-project.org/web/packages/foreign/index.html http://cran.r-project.org/web/packages/foreign/index.html On Thu, Feb 4, 2010 at 5:31 PM, Alex Bryant wrote: Hi, I have a need to process (in real-time) a large number of .sas7bdat files from within R. The problem is I don't want to convert these files to .xpt (transport) every time. So just checking if anyone has a (viable) way to read .sas7bdat files directly into R? Thank You. //-- // Alex Bryant // Software Developer // Integrated Clinical systems // 908-996-7208 Confidentiality Note: This e-mail, and any attachment to...{{dropped:13}} __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
Alex Bryant wrote: Hi, I have a need to process (in real-time) a large number of .sas7bdat files from within R. The problem is I don't want to convert these files to .xpt (transport) every time. So just checking if anyone has a (viable) way to read .sas7bdat files directly into R? SAS now advertises some sort of R support (see http://support.sas.com/rnd/app/studio/Rinterface2.html), so maybe you could get SAS to convert them to a native R format. I think you won't find a way for R to read a SAS proprietary format. Duncan Murdoch __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
On Feb 4, 2010, at 5:31 PM, Alex Bryant wrote: Hi, I have a need to process (in real-time) a large number of .sas7bdat files from within R. The problem is I don't want to convert these files to .xpt (transport) every time. So just checking if anyone has a (viable) way to read .sas7bdat files directly into R? I believe (on the basis of what is written on the NCHS/NHANESwebsite) that if you are a Windows user, which I am only under duress, you can get at such data with a free product that SAS makes available: http://www.sas.com/apps/demosdownloads/sassysview_PROD_8.2_sysdep.jsp?packageID=000176 Whether that product can be called as a system() executable with arguments, I have no idea. -- David Winsemius, MD Heritage Laboratories West Hartford, CT __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Reading sas7bdat files directly
David Winsemius wrote: On Feb 4, 2010, at 5:31 PM, Alex Bryant wrote: Hi, I have a need to process (in real-time) a large number of .sas7bdat files from within R. The problem is I don't want to convert these files to .xpt (transport) every time. So just checking if anyone has a (viable) way to read .sas7bdat files directly into R? I believe (on the basis of what is written on the NCHS/NHANESwebsite) that if you are a Windows user, which I am only under duress, you can get at such data with a free product that SAS makes available: http://www.sas.com/apps/demosdownloads/sassysview_PROD_8.2_sysdep.jsp?packageID=000176 Whether that product can be called as a system() executable with arguments, I have no idea. The SAS viewer has a pretty pathetic data export feature that won't even produce valid csv files if you have any unmatched quotes in character variables. I don't think it's very good about exporting metadata either. Frank -- Frank E Harrell Jr Professor and ChairmanSchool of Medicine Department of Biostatistics Vanderbilt University __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.