using the xpt2loc macro to convert multiple .xpt files to sas datasets

[David20]

I  have multiple .xpt files (v8) created using the loc2xpt macro which I need  to convert to sas files using the xpt2loc macro.  I am new to sas but I have managed to use the macro to convert a single file:

%xpt2loc ( libref   = work, memlist  = _all_ , filespec = 'c:/myfile.xpt') 

However I cannot loop through a directory and select all the .xpt file names to use in the filespec. I have trying to adapt the code below which I have found on these boards  but so far have been unsuccessful. 

%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;

Any help would be appreciated. 

[FreelanceReinh]

Hello @David20 and welcome to the SAS Support Communities!

The first of the two PUT statements in your code is not correct. It should rather look like this:

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

Also, make sure that sasfile (which is a libref used in your PROC COPY statement) points to the destination folder of the SAS datasets to be created, e.g., with a LIBNAME statement:

libname sasfile 'C:\Temp';

(Replace C:\Temp by the correct path to the existing destination folder.)

After the DATA step has produced the code file (consisting of LIBNAME and PROC COPY statements) you may want to look into that file (just to verify quickly that the produced code is syntactically correct), which will reside in your WORK library with a name like #LNxxxxx (where xxxxx is a number such as 00082). The full path will be shown in the log of the DATA step after the line

NOTE: The file CODE is:

Open the file with Notepad.

Then "%include" (i.e. execute) the code file by submitting

%inc code;

Alternatively, you can combine the two approaches and produce a code file containing XPT2LOC macro calls (instead of LIBNAME and PROC COPY statements). To do this, a single PUT statement would replace the two existing PUT statements:

put '%xpt2loc(libref = sasfile, memlist = _all_, filespec = ''' "&path" '\' filename +(-1) ''')';

(The other steps -- quick check of the code file and %inc statement -- are the same as above.)

In both cases there is a potential issue: Both the PROC COPY statements and the XPT2LOC macro calls would overwrite existing datasets with the same name in the destination folder without asking. This means, if two of the .xpt files happened to contain datasets with the same name, you would end up with the dataset from the second .xpt file. So, you should check the documentation of the .xpt files (if any) and the SAS log to make sure that this does not happen. (Otherwise, use different destination folders to avoid the name conflict.)

[ahaoya]

Thank you so much. The single quote in macro is confusion.

[Tom]

It should be trivial to change that program to generate the code you want.

%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 '%xpt2loc(libref=work,memlist=_all_,filespec=' filename :$quote. ')' ; 
run;
%include code / source2;

[David20 · Accepted solution]

Many thanks for both of these replies. This has helped a lot and I have been able to adapt these to create a working solution using the XPT2LOC macro, as I believe the proc copy statement will not work with v8 transport files created with the LOC2XPT macro.