DATA Step, Macro, Functions and more

Convert .XPT to SAS Datasets

Accepted Solution Solved
Reply
Regular Contributor
Posts: 247
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: 7,860

Re: Convert .XPT to SAS Datasets

Posted in reply to djbateman

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: 7,860

Re: Convert .XPT to SAS Datasets

Posted in reply to djbateman

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: 247

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: 7,860

Re: Convert .XPT to SAS Datasets

Posted in reply to djbateman

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: 53

Re: Convert .XPT to SAS Datasets

Posted in reply to djbateman

did u get solution with macro?

New Contributor
Posts: 2

Re: Convert .XPT to SAS Datasets

Posted in reply to djbateman

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

New User
Posts: 1

Re: Convert .XPT to SAS Datasets

Posted in reply to JacquesCr

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

Valued Guide
Posts: 2,190

Re: Convert .XPT to SAS Datasets

Posted in reply to djbateman
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

Posted in reply to Rajesh0136

 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
  • 9 replies
  • 54174 views
  • 2 likes
  • 7 in conversation