topic Re: Convert .XPT to SAS Datasets in SAS Programming

Convert .XPT to SAS Datasets

djbateman — Tue, 05 Feb 2013 14:31:49 GMT

I was given a folder full of .xpt files. Is there code that can read in the XPT folder and convert them into SAS datasets (.sas7bdat)? I have never dealt with XPT files before, so I don't have a clue where to start. Here is a sample of the code that I have been attempting:

libname xptfile xport 'C:\XPT Datasets';

libname sasfile 'C:\SAS Datasets';

data sasfile.ca125cec;

set xptfile.ca125cec;

run;

data sasfile.ca125cen;

set xptfile.ca125cen;

run;

The problem with this is that the xptfile library contains nothing when read in. The log says that the library was successfully created, but when I click on the library icon to open it, it gives me an error message: "ERROR, Invalid file, C:\XPT Datasets." Therefore, when the data steps are executed, nothing goes in, and nothing goes out.

Any thoughts?

Re: Convert .XPT to SAS Datasets

Tom — Tue, 05 Feb 2013 14:37:57 GMT

If the files a V5 transport files then you can point to them with a LIBNAME statement using the XPORT engine, but you have to point to the individual .XPT files, not the folder that contains them.

libname sasfile 'C:\SAS Datasets';

libname xptfile xport 'C:\XPT Datasets\ca125cec.xpt' access=readonly;

proc copy inlib=xptfile outlib=sasfile;

run;

libname xptfile xport 'C:\XPT Datasets\ca125cen.xpt' access=readonly;

proc copy inlib=xptfile outlib=sasfile;

run;

Re: Convert .XPT to SAS Datasets

djbateman — Tue, 05 Feb 2013 15:25:00 GMT

Tom,

Do you have a suggestion for running that code through a macro or a loop if I have dozens of .xpt files? I usually do something like this:

proc sql;

select count(distinct memname) into :numfiles from sashelp.vtable where libname='XPTFILE';

%let numfiles=%sysfunc(compress(&numfiles.));

select distinct memname into :file1-:file&numfiles. from sashelp.vtable where libname='XPTFILE';

quit;

Then I would run your code through a loop. However, this does not work because the xport option in the libname statement does not allow xptfile to appear in the vtable. I don't understand XPT, so I don't know where to find a list of the files in that library. Any thoughts?

Re: Convert .XPT to SAS Datasets

Tom — Tue, 05 Feb 2013 17:09:07 GMT

Get the operating system to tell you the names. If your system doesn't allow that then you can use the DOPEN, DREAD functions.

%let path=C:\XPT Datasets;

filename xptfiles pipe "dir /b ""&path\%str(*).xpt"" " ;

filename code temp;

data files;

infile xptfiles truncover;

input filename $100. ;

file code;

put 'filename xptfile "&path\' filename +(-1) '" access=readonly;';

put 'proc copy inlib=xptfile outlib=sasfile; run;' ;

run;

libname sasfile '....';

%inc code / source2 ;

Re: Convert .XPT to SAS Datasets

JacquesCr — Fri, 09 Oct 2015 09:46:54 GMT

Hi djbateman, see my solution...

/* allocating output directories */
libname sasfile 'J:\SDTM\c_DefineXML\Define-XML-2-0_RP20140424\sdtm\sas_output';

/* create filename 'xptprgs' to capture all *.xpt files */
filename xptprgs pipe "dir /b J:\SDTM\c_DefineXML\Define-XML-2-0_RP20140424\sdtm\xpt_input\*.xpt";

/* create an extra variable 'xptprgs' to contain only the names of the xpt-files */
data work.xptprgs;
length filepath $20;

infile xptprgs length=reclen;

input filepath $varying256. reclen ;

xptprgs=reverse(substr(trim(left(reverse(filepath))),5));

run;

/* create macro variable 'xptprgs' to capture all individual xpt file names */
proc sql;
select
xptprgs

into:

xptprgs separated by ' '
from
work.xptprgs
;
quit;

%put &xptprgs;

/* loop through all individual files */
%macro xpt2sas;

%do i=1 %to %sysfunc(countw(&xptprgs));

libname xptfile xport "J:\SDTM\c_DefineXML\Define-XML-2-0_RP20140424\sdtm\xpt_input\%scan(&xptprgs, &i).xpt" access=readonly;

proc copy inlib=xptfile outlib=sasfile;
run;

%end;

run;
%mend xpt2sas;

%xpt2sas;

Kr,

Jacques

Re: Convert .XPT to SAS Datasets

Peter_C — Fri, 09 Oct 2015 19:43:01 GMT

Why not just wrap this in a macro loop
libname xptlib xport "&path2xpt_file" access=readonly;
* clear receiving library (work here) ;
Proc datasets mt= data nolist kill lib= work ; quit;
* copy all tables in xpt to work lib ;
Proc copy in= xptlib out=work ; run ;
* deal with the tables in WORK;

Re: Convert .XPT to SAS Datasets

Rajesh0136 — Fri, 11 Dec 2015 19:06:22 GMT

I have the same issue copy and convert the XPTs. I am using the below code.

libname sasfile 'C:\SAS Datasets';
libname xptfile xport 'C:\XPT Datasets\ca125cec.xpt' access=readonly;
proc copy inlib=xptfile outlib=sasfile;
run;

I am getting the below note. I was able to create the Libray with XPTs, Why the file is invalid.

NOTE: Input library XPTFILE is sequential.
ERROR: Invalid file, C:\XPT Datasets\ca125cec.xpt.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
real time 2.56 seconds
cpu time 2.38 seconds

NOTE: The SAS System stopped processing this step because of errors.

May I know how to avoid the above error and get the XPTs converted.

Re: Convert .XPT to SAS Datasets

JacquesCr — Fri, 18 Dec 2015 13:36:36 GMT

Hi Rajesh,

I had a look at your code and did not find anything strange except that i changed the single quotes to double quotes and then it worked.

libname xptfile xport "C:\XPT Datasets\ca125cec.xpt" access=readonly;

Else, I did not see anything peculiar.

Hope this helps.

Jacques

Re: Convert .XPT to SAS Datasets

paddyb — Fri, 13 Jan 2017 06:35:57 GMT

did u get solution with macro?

Re: Convert .XPT to SAS Datasets

sdunn8898 — Thu, 07 Sep 2017 18:22:48 GMT

THANK YOU!!! This is PERFECT and exactly what I needed.

Re: Convert .XPT to SAS Datasets

ajkalale — Sun, 07 Oct 2018 23:25:06 GMT

Wow, What a simple solution!

NO headache noe

Re: Convert .XPT to SAS Datasets

ritajhuang — Sat, 15 Feb 2020 23:29:52 GMT

Hi everyone,

I'm trying to read to .xpt data using the SAS studio (University Edition)

I'm not sure how should I write this code... Please kindly advise, thank you!

Note: My .xpt files locate at C:/SASUniversityEdition/myfolders/Bankdata

libname sasfile 'C:\folders\myfolders\Bankdata';

libname xptfile xport 'C:\XPT Datasets\CALL1103.xpt' access=readonly;

proc copy inlib=xptfile outlib=sasfile;

run;

libname xptfile xport 'C:\XPT Datasets\CALL1103.xpt' access=readonly;

proc copy inlib=xptfile outlib=sasfile;

run;

Thank you very much in advance!

Re: Convert .XPT to SAS Datasets

Tommer — Wed, 09 Feb 2022 21:22:00 GMT

This was so helpful! Solved my problem!

Re: Convert .XPT to SAS Datasets

j1914 — Wed, 27 Jul 2022 15:04:12 GMT

Hi! How do you set the data name after you complete this step?

Re: Convert .XPT to SAS Datasets

DrAbhijeetSafai — Fri, 10 Nov 2023 10:26:38 GMT

Simply Amazing! Thanks!

- Dr. Abhijeet Safai