Convert .XPT to SAS Datasets

[djbateman]

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?

[Tom · Accepted solution]

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;

[djbateman]

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?

[Tom]

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 ;

[paddyb]

did u get solution with macro?

[ajkalale]

Wow, What a simple solution! 

NO headache noe

[j1914]

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

[DrAbhijeetSafai]

Simply Amazing! Thanks!

- Dr. Abhijeet Safai

[JacquesCr]

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

[sdunn8898]

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

[Tommer]

This was so helpful! Solved my problem!

[Peter_C]

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;

[Rajesh0136]

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.

[JacquesCr]

 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

[ritajhuang]

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!