suggestions about import SAS results to R.

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

suggestions about import SAS results to R.

Thread (12 messages)