DATA Step, Macro, Functions and more

Convert .XPT to SAS Datasets

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Convert .XPT to SAS Datasets

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?


Accepted Solutions
Solution
‎02-05-2013 09:37 AM
Super User
Super User
Posts: 6,502

Re: Convert .XPT to SAS Datasets

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;


View solution in original post


All Replies
Solution
‎02-05-2013 09:37 AM
Super User
Super User
Posts: 6,502

Re: Convert .XPT to SAS Datasets

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;


Regular Contributor
Posts: 220

Re: Convert .XPT to SAS Datasets

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?

Super User
Super User
Posts: 6,502

Re: Convert .XPT to SAS Datasets

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 ;


Contributor
Posts: 33

Re: Convert .XPT to SAS Datasets

did u get solution with macro?

New Contributor
Posts: 2

Re: Convert .XPT to SAS Datasets

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

Valued Guide
Posts: 2,175

Re: Convert .XPT to SAS Datasets

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;


Occasional Learner
Posts: 1

Re: Convert .XPT to SAS Datasets

[ Edited ]

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.

 

 

New Contributor
Posts: 2

Re: Convert .XPT to SAS Datasets

 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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 40941 views
  • 1 like
  • 6 in conversation