[R] suggestions about import SAS results to R.
Yuan, Rebecca
rebecca.yuan at bankofamerica.com
Thu Jan 3 16:53:26 CET 2013
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
> 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@
> > wrote:
>> 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@
> ]
>> Sent: Wednesday, January 02, 2013 5:25 PM
>> To: Yuan, Rebecca
>> Cc: R help;
> sas-l at .uga
>> 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@
> > wrote:
>>> 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
>>> ---------------------------------------------------------------
>>>> source("testss.r")
>>> 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@
> [mailto:
> mehmet.suzen@
> ] On Behalf
>>> Of Suzen, Mehmet
>>> Sent: Wednesday, January 02, 2013 4:14 PM
>>> To: David Winsemius
>>> Cc: Yuan, Rebecca; R help;
> sas-l at .uga
>>> Subject: Re: [R] suggestions about import SAS results to R.
>>>
>>>> On Jan 2, 2013, at 12:37 PM, Yuan, Rebecca wrote:
>>>>> I am wondering if there is an efficient way to read SAS data
>>>>> directly in R, or what would be a better connection between SAS
>>>>> and R if I need to use R to deal with data achieved from SAS?
>>>>>
>>>
>>> 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}}
>>>
>>> ______________________________________________
>>>
> R-help@
> 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...{{dropped:6}}
>
> ______________________________________________
> R-help@
> 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.
-----
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}}
More information about the R-help
mailing list