BookmarkSubscribeRSS Feed
ari
Quartz | Level 8 ari
Quartz | Level 8

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? 

4 REPLIES 4
ballardw
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11
* 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
Super User Tom
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3814 views
  • 0 likes
  • 5 in conversation