Skip to content

suggestions about import SAS results to R.

12 messages · Yuan, Rebecca, Suzen, Mehmet, Ista Zahn +3 more

Original
David Winsemius
#
On Jan 2, 2013, at 12:37 PM, Yuan, Rebecca wrote:

            

      
      
      
    

        
(Didn't type this with the intent of shouting but decided not to revise after fixing my sticky shift key.)  THERE IS AN ENTIRE MANUAL ON EXPORT AND IMPORT STRATEGIES:

On my machine the link to it  "R Data Import/Export" appears if one uses the drop-down RHelp option from the Help menu:

It links to http://127.0.0.1:29713/doc/manual/R-data.html

But the details of where you should find it will depend on your GUI and OS setup. At any rate please find and read this fine manual.

Your other question is also addressed in that manual.

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Suzen, Mehmet
    

    

      
      #
    

  


  

          

      
      
      
    

        
You may try foreign and SASxport packages:
http://cran.r-project.org/web/packages/foreign/index.html
http://cran.r-project.org/web/packages/SASxport/index.html

But I heard that there might be some issues with the newest SAS
version but give it a try...

  
  


  


      

    

    

      
      

        


  

        

      Yuan, Rebecca
    

    

      
      #
    

  


  

      
Hello all,

Thanks for the suggestions.

I tried to import data from sas to R using foreign package. In the first step, I tried to use read.ssd() to convert the sas file sales.sas7bdat to SAS Transport formats. But I got an error message as 
---------------------------------------------------------------

            

      
      
      
    

        
Error in file.symlink(oldPath, linkPath) : 
  symbolic links are not supported on this version of Windows
---------------------------------------------------------------

While in testss.r, 

---------------------------------------------------------------
require(foreign)
rm(list=ls())
sashome <- "C:/Program Files/SAS/SASFoundation/9.2/"
checkfile <- read.ssd("C:/Documents and Settings/test","sales",sascmd = file.path(sashome, "sas.exe"))
---------------------------------------------------------------

I am using windows xp and R 2.15.2. 

Thanks!

Rebecca




-----Original Message-----
From: mehmet.suzen at gmail.com [mailto:mehmet.suzen at gmail.com] On Behalf Of Suzen, Mehmet
Sent: Wednesday, January 02, 2013 4:14 PM
To: David Winsemius
Cc: Yuan, Rebecca; R help; sas-l at listserv.uga.edu
Subject: Re: [R] suggestions about import SAS results to R.

            

      
      
      
    

        
You may try foreign and SASxport packages:
http://cran.r-project.org/web/packages/foreign/index.html
http://cran.r-project.org/web/packages/SASxport/index.html

But I heard that there might be some issues with the newest SAS version but give it a try...

----------------------------------------------------------------------
This message, and any attachments, is for the intended r...{{dropped:2}}

  
  


  


      

    

    

      
      

        


  

        

      Ista Zahn
    

    

      
      #
    

  


  

      
Hi Rebecca,

I've had success with the sas7bdat package (
http://cran.r-project.org/web/packages/sas7bdat/ )

Best,
Ista

On Wed, Jan 2, 2013 at 5:15 PM, Yuan, Rebecca

        
<rebecca.yuan at bankofamerica.com> wrote:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Yuan, Rebecca
    

    

      
      #
    

  


  

      
Hello Ista,

Thanks for mention this package, however, it will not support big file, where I got the error message:

----------------------
Error in read.sas7bdat("C:/Documents and Settings/test.sas7bdat") : 
  big endian files are not supported
----------------------

Cheers,

Rebecca

-----Original Message-----
From: Ista Zahn [mailto:istazahn at gmail.com] 
Sent: Wednesday, January 02, 2013 5:25 PM
To: Yuan, Rebecca
Cc: R help; sas-l at listserv.uga.edu
Subject: Re: [R] suggestions about import SAS results to R.

Hi Rebecca,

I've had success with the sas7bdat package ( http://cran.r-project.org/web/packages/sas7bdat/ )

Best,
Ista

        
On Wed, Jan 2, 2013 at 5:15 PM, Yuan, Rebecca <rebecca.yuan at bankofamerica.com> wrote:

            

      
      
      
    

  
  


  


      

    

    

      
      

        


  

        

      David Winsemius
    

    

      
      #
    

  


  

      
On Jan 2, 2013, at 2:44 PM, Yuan, Rebecca wrote:

        

            

      
      
      
    

        
That does not mean that "big files" are not supported.

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Michael Dewey
    

    

      
      #
    

  


  

      
At 22:44 02/01/2013, Yuan, Rebecca wrote:

            

      
      
      
    

        
Rebecca, have a look at

http://en.wikipedia.org/wiki/Endianness

            

      
      
      
    

        
Michael Dewey
info at aghmed.fsnet.co.uk
http://www.aghmed.fsnet.co.uk/home.html

  
  


  


      

    

    

      
      

        


  

        

      Ista Zahn
    

    

      
      #
    

  


  

      
Well it was worth a try. If you haven't solved the problem some other
way I suggest exporting from SAS to a plain text format like .csv and
read into R with read.table.

Best,
Ista

On Wed, Jan 2, 2013 at 5:44 PM, Yuan, Rebecca

        
<rebecca.yuan at bankofamerica.com> wrote:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Frank E Harrell Jr
    

    

      
      #
    

  


  

      
SAS' export to csv is buggy (improper quote matching, etc.).  Instead
consider having SAS create a version 5 transport file, then use the Hmisc
package's sasxport.get function to handle labels, etc.
Frank

Ista Zahn wrote

            

      
      
      
    

        

      
      
    

        

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

        

            

      
      
      
    

        

      
      
    

        
-----
Frank Harrell
Department of Biostatistics, Vanderbilt University
--
View this message in context: http://r.789695.n4.nabble.com/suggestions-about-import-SAS-results-to-R-tp4654444p4654521.html
Sent from the R help mailing list archive at Nabble.com.

  
  


  


      

    

    

      
      

        


  

        

      Yuan, Rebecca
    

    

      
      #
    

  


  

      
Hello all,

Thanks very much for your kind help!

So far, I have tried three options and turned out that none of them working in my case. I am not sure if I did anything abnormal to make it that hard to import the data from SAS result to R.

I am running on SAS 9.2 to get the sales.sas7bdat and test_20130103.xls files. The excel file is exported with multiple sheets, where each sheet is automatically generated by group:

%macro raw_export_to_excel(recordtime);

ods listing close;
ods tagsets.excelxp file="C:\Documents and Settings\test_&recordtime..xls" style=statistical
    options(sheet_interval='bygroup');

proc sort	data = sales;
by age, state;
where age in (20,21,22,23);
run;

proc print data=sales;
by age;
run;
ods tagsets.excelxp close;

%mend raw_export_to_excel;
%raw_export_to_excel(%sysfunc(date(),yymmddn8.));

In the local machine, when I open the excel file in office 2007 in windows xp, I got a warning message said "The file you are trying to open, 'test_20130103.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

I choose yes and the file is opened and I can see data in each sheet. 

Then I start to import either sales.sas7bdat or test_20130103.xls files to R with three approaches:

----------------------
Approach 1: sas7bdat pack

By:	read.sas7bdat("C:/Documents and Settings/sales.sas7bdat")

The error message shows that "big endian files are not supported"
----------------------

I do not understand why it says bid endian files are not supported. The sas file was generated on the server, I am using windows xp, does that mean there is a mismatch of the bytes of the files. Or this is related to the warning message when opening the excel file.

----------------------
Approach 2: foreign pack

By:
require(foreign)
rm(list=ls())
sashome <- "C:/Program Files/SAS/SASFoundation/9.2/"
checkfile <- read.ssd("C:/Documents and Settings/","sales",sascmd = file.path(sashome, "sas.exe"))

To convert .sas7bdat file to SAS Transport format.

The error message shows that " Error in file.symlink(oldPath, linkPath) : symbolic links are not supported on this version of Windows"
----------------------

Is this because of windows xp is not support in symbolic links? Maybe I can generate the transport format in SAS instead of calling from R.


----------------------
Approach 3: RODBC pack

By:
require('RODBC')
rm(list=ls())
channel <- odbcConnectExcel("c://Documents and Settings/test_20130103.xls",readOnly=TRUE);
age20 <- sqlFetch(channel,"age=20",as.is=TRUE);
odbcClose(channel)

The error message shows that "External table is not in the expected format."
----------------------

Is this related to the warning message in exporting data to excel file?

Thanks,

Rebecca





-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of Frank Harrell
Sent: Thursday, January 03, 2013 9:08 AM
To: r-help at r-project.org
Subject: Re: [R] suggestions about import SAS results to R.

SAS' export to csv is buggy (improper quote matching, etc.).  Instead consider having SAS create a version 5 transport file, then use the Hmisc package's sasxport.get function to handle labels, etc.
Frank

Ista Zahn wrote

            

      
      
      
    

        

      
      
    

        

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

        

            

      
      
      
    

        

      
      
    

        
-----
Frank Harrell
Department of Biostatistics, Vanderbilt University
--
View this message in context: http://r.789695.n4.nabble.com/suggestions-about-import-SAS-results-to-R-tp4654444p4654521.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
R-help at 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.

----------------------------------------------------------------------
This message, and any attachments, is for the intended r...{{dropped:2}}

  
  


  


      

    

    

      
      

        


  

        

      Frank E Harrell Jr
    

    

      
      #
    

  


  

      
Not clear why you responded to my note since you were not using the solution
I suggested.
Frank

By the way if you need to read SAS7BDAT FILES frequently it is worth the
licensing fee for STAT/Transfer, which can create binary R .rda objects.  I
have code that finishes the job by putting labels on individual variables.
Frank




-----
Frank Harrell
Department of Biostatistics, Vanderbilt University
--
View this message in context: http://r.789695.n4.nabble.com/suggestions-about-import-SAS-results-to-R-tp4654444p4654554.html
Sent from the R help mailing list archive at Nabble.com.