BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Chengyiw
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

 

 

View solution in original post

11 REPLIES 11
art297
Opal | Level 21

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

 

Chengyiw
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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.

Chengyiw
Fluorite | Level 6

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?

Tom
Super User Tom
Super User

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.

 

 

Chengyiw
Fluorite | Level 6

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.

Chengyiw
Fluorite | Level 6

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!

ChrisNZ
Tourmaline | Level 20

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.

 

Chengyiw
Fluorite | Level 6

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?

ChrisNZ
Tourmaline | Level 20

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

Chengyiw
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 5033 views
  • 2 likes
  • 4 in conversation