convert excel with multiple sheets to xpt format

[ari]

I am wondering if its possible convert an excel file with multiple sheets in it  to .xpt format. I could not find any information online. Any leads about the convertion from excel (with multiple sheets) to xpt format? 

[ballardw]

Do you mean a SAS transport format file? The tools SAS provides for that start from SAS data sets. So to involve SAS you would need to import the data into SAS and then create the XPT file from those datasets.

There may be some third party software tools that would do that but the only ones I'm personally familiar with that may have done that quit working when Windows 7 came out and would not have supported XLSX files.

[RW9]

Why would you want to go directly from an unstructured, uncontrolled poor data medium such as Excel, into a very old format such as XPT?  I mean you really are asking for problems and QC issues there.  I mean how would you go about ensuring that the data you have in Excel (bearing in mind all the "features" therein) matches what is in the XPT?

[rogerjdeangelis]

* sending multiple sheets to one version 5 xport file;

*create a excel file with a couple of sheets;

%utlfkil(d:/xls/twotab.xlsx); * delete if exist;
libname xel "d:/xls/twotab.xlsx";
data xel.tab1 xel.tab2;
  set sashelp.class;
run;quit;

* examine the workbook;
proc contents data=xel._all_;
run;quit;

* export to version 5 xpt;
LIBNAME xptout xport 'd:/xpt/twotab.xpt';
proc copy in=xel out=xptout memtype=data;
select tab1 tab2;
run;
libname xel clear;

It is not necessary to know the names of the tabs,
but the code is a little more complex, 

*check;
data tab1;
 set xptout.tab1;
run;quit;

*check;
data tab2;
 set xptout.tab2;
run;quit;

[Tom]

Use PROC COPY.

libname in xlsx "my excel file.xlsx";
libname out xport "my v5 transport file.xpt";
proc copy inlib=in outlib=out;
run;

But if the sheetnames and column headers in your Excel file do not meet the 8 character name limits of a SAS V5 transport file you will have errors.