topic Re: Converting XPT to SAS and change dataset name in SAS Programming

Converting XPT to SAS and change dataset name

Chengyiw — Thu, 17 May 2018 18:10:01 GMT

Hi all,

I have at least 10 XPT files that I want to convert to SAS. It is easy. However, I realize that all datasets ended up with the same name in SAS. One way to do it is to create 10 different paths for these SAS files. But I wonder there is a way to change the dataset name while converting XPT to SAS so that I can store all datasets in one folder.

Thanks!

Re: Converting XPT to SAS and change dataset name

art297 — Thu, 17 May 2018 19:56:16 GMT

If you can use proc datasets, rather than proc copy, I'd think you can incorporate both of that proc's change and copy statements.

Art, CEO, AnalystFinder.com

Re: Converting XPT to SAS and change dataset name

Tom — Fri, 18 May 2018 02:30:15 GMT

We need a lot more information. For example what type of export files are you working with? Those made with the XPORT engine or those made with PROC CPORT?

Does each "XPT" file contain only one dataset? Both the XPORT and CPORT formats allow for multiple datasets to appear in the same physical file.

Re: Converting XPT to SAS and change dataset name

ChrisNZ — Fri, 18 May 2018 03:33:36 GMT

Just run 2 procedures instead of one for each table.

proc cimport data=DEST.TABLE infile=XPT1; run;
proc datasets lib=DEST; change TABLE=TABLE1; quit;

It won't run any slower.

Re: Converting XPT to SAS and change dataset name

Chengyiw — Fri, 18 May 2018 16:34:56 GMT

I'm not sure how to use proc datasets to convert XPT to SAS. Can you give me an example?

Re: Converting XPT to SAS and change dataset name

Chengyiw — Fri, 18 May 2018 16:40:01 GMT

So I was trying to convert multiple XPT files in one folder into SAS datasets using the macro below. And I used proc copy basically. Because all XPT files ended up with the same name, I only one SAS dataset after running the macro. But I finally was able to understand the macro and added a data step in the macro to change each dataset's name. It worked!

/* Macro using PROC COPY and the XPORT engine for reading transport files*/
%macro drive(dir,ext,out);

%let filrf=mydir;

/* Assigns the fileref of mydir to the directory and opens the directory */
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));

/* Returns the number of members in the directory */
%let memcnt=%sysfunc(dnum(&did));

/* Loops through entire directory */
%do i = 1 %to &memcnt;

/* Returns the extension from each file */
%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

/* Checks to see if file contains an extension */
%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&ext) %then %do;

/* Checks to see if the extension matches the parameter value */
/* If condition is true, submit PROC COPY statement */
%if (%superq(ext) ne and %qupcase(&name) = %qupcase(&ext)) or
(%superq(ext) = and %superq(name) ne) %then %do;

libname old xport "&dir.\%qsysfunc(dread(&did,&i))";
libname new "&out";
proc copy in=old out=new;
run;

data new.CNTY&i;
set new.trnsport;
run;
%end;
%end;
%end;

/* Close the directory */
%let rc=%sysfunc(dclose(&did));
/* END MACRO */
%mend drive;

/* Macro call */

/*First parameter is the source folder, the second parameter is extension being */
/*searched for, and the third parameter is the target directory for the */
/*converted files. */

%drive(C:\temp,xpt,C:\temp\)

As you mentioned proc cport, what is the difference between proc copy and proc cport?

Re: Converting XPT to SAS and change dataset name

Chengyiw — Fri, 18 May 2018 16:42:59 GMT

Please see my reply to Tom. I know it's not going to take much time to add one more step, but I wasn't able to understand the macro in the beginning. But yes I edited the macro and added one more step and was able to change names now.

As you mentioned proc cimport, can you enlighten me on the differences among proc copy, cport, and cimport?

Re: Converting XPT to SAS and change dataset name

ChrisNZ — Sat, 19 May 2018 04:52:25 GMT

1. Do not recreate a table just to rename it. Use proc datasets

2. proc port => create XPT file

proc cimport => read XPT file

proc copy => copy data sets

Re: Converting XPT to SAS and change dataset name

Tom — Sat, 19 May 2018 12:06:13 GMT

You are using the XPORT libname engine to access SAS V5 Transport files. That is the most portable of the SAS transport formats because it's structure is published. But it has limitations of only supporting datasets that conform to the V5 structure. (8 character variable and member names and 200 character variables). There are also SAS macros that can read/write a newer SAS V8 transport files that support longer names and longer character variables. The CPORT and CIMPORT procs create a different transport format. That format will allow you to transfer catalogs in addition to datasets. But that format is not published and is also not backwards compatible. You can't read a transport file made a newer version of SAS using an old version of SAS.

You can simplify your macro a little. First you can move the libname statement that create the NEW libref out of the loop as it is not changing. Then you can eliminate the PROC COPY step and just read the dataset from transport file directly with a data step.

libname old xport "&dir.\%qsysfunc(dread(&did,&i))";
data new.CNTY&i;
set old.trnsport;
run;

Are you sure that all of the transport files contain a TRNSPORT dataset? Do any of them have more than one dataset in them?

You can make your macro more flexible by reading the metadata from your transport files. You can use PROC CONTENTS on the NEW libref or query the DICTIONARY metadata tables.

Re: Converting XPT to SAS and change dataset name

Chengyiw — Wed, 23 May 2018 16:16:36 GMT

Thanks a lot, Tom! I'm sure all transport files contain a trnsport dataset because when I open the xptfile library in SAS, there is only one trnsport dataset in there.

Re: Converting XPT to SAS and change dataset name

Chengyiw — Wed, 23 May 2018 16:18:06 GMT

Thanks, Chris! I agree that proc datasets is better than data step here.

Re: Converting XPT to SAS and change dataset name

Chengyiw — Wed, 23 May 2018 16:27:31 GMT

Please ignore my previous reply. You're right. Not all transport files contain a trnsport dataset - some of them do, others have different names. Thank you for pointing it out!