Saturday, February 14, 2015

Data Transport Across Different Platforms: Creating and Reading the SAS transport files (.xpt, .cpt, .dat)

Since there are different computer platforms (Windows - PC, Unix, Linux, OpenVMS), it is often necessary to create the transport file so that the data file created in one platform can be used in another platform. SAS is the primary data format used in pharmaceutical/biotech companies and SAS data format is generally the data format in regulatory submissions. In an early posting "Submit the Clinical Trial Datasets to FDA: Using the right .xpt file format", I discussed that for FDA submission, the SAS transport file must be generated using xport engine (i.e, using PROC COPY).

Creating .xpt file using PROC COPY

While possible, this should be used a standard way to create the SAS transport file. 

libname source 'SAS-library-on-sending-host';   /*indicating where the original SAS data sets (in .sas7bdat format) are*/
libname xptout xport 'filename-on-sending-host'/*indicating where the .xpt files to be stored */
proc copy in=source out=xptout memtype=data;

Reading .xpt file generated with PROC COPY

For recipients, the SAS transport file in .xpt format needs to be converted back to SAS data format so that the further data manipulation / analysis can be performed. To read the .xpt file generated using PROC COPY, we will still use PROC COPY:

libname sasfolder "SAS-library-on-receiving-host"/*indicating where the converted SAS data sets (in .sas7bdat format) to be stored*/
libname xptfile xport '\folder\xyz.xpt' access=readonly; /*indicating which the .xpt files to be read in */
proc copy inlib=xptfile outlib=sasfolder;
We can also use SAS data step to read the .xpt file. Or we may just read .xpt file in without creating a permanent SAS data set.  
libname xportin xport '\folder\transport-file-name.xpt';
libname target '\folder\';
data target.transport-file-name; *create a permanent SAS data set;
set xportin.transport-file-name ;
data transport-file-name;  *create a temporary SAS data set;
set xportin.transport-file-name;

Creating .xpt file using PROC CPORT

In SAS, the transport file can be generated with PROC CPORT (not recommended for regulatory submission) with the following statement.  
          filename xptfile "c:\temp\test.xpt";
libname source "c:\sourcedata\";
***For converting a data set;
proc cport library=source file=xptfile memtype=data;
***For converting a format catalog;
proc cport library=source file=xptfile memtype=catalog;
If we strictly follow the name convention, the SAS transport file created with PROC CPORT should use the file extension .cpt (versus .xpt for SAS transport file generated with PROC XPORT). Unfortunately, people may not follow the conversion and use .xpt for SAS transport files generated either with PROC XPORT or PROC CPORT). 

Reading .xpt file generated with PROC CPORT

To read the .xpt file created by PROC CPORT, PROC CIMPORT:
FILENAME xpt 'transport_file_name.xpt';
libname dest 'destination_directory_name';
Read the transport file in other format/extensions (.dat, .cpt). 

We may also receive data in other format for example, the transport data file may be in .dat format. Data with .DAT extension indicates a data file that has arbitrary data. That means it’s not associated with any one particular program or application. The following program can be used to read the data into SAS. 
libname outlib 'C:\TEMP';
filename infile 'TAL050003XF.dat';
proc cimport library=outlib file=infile;
The file with .cpt extension is the transport file generated using SAS PROC CPORT as described above. 

No comments: