topic Re: Using an AUTO call macro %xpt2loc to convert datasets in a library in SAS Enterprise Guide

Using an AUTO call macro %xpt2loc to convert datasets in a library

venkatagopinath — Wed, 21 Apr 2021 18:22:25 GMT

I used the auto call program %loc2xpt to convert sas datasets to XPT using the below code.

proc sql;
create table sdtmDomains as
select libname
,memname
from dictionary.tables
where libname eq 'SDTM'
order by memname;
quit;

data _null_;
set sdtmDomains end=eof;
call symput('domain_' || strip(put(_n_,2.))
,strip(lowcase(memname))
);
if eof then
call symput('domainCnt',strip(put(_n_,2.)));
run;

%macro xpt;
%do idx=1 %to &domainCnt;

filename xptfile "c:\public\sdtm\&&domain_&idx...xpt";
%loc2xpt(libref=sdtm
,memlist=&&domain_&idx
,filespec=xptfile
);
%end;
%mend xpt;
%xpt

I am trying to change the above code to use with another auto call macro %xpt2loc to convert the xpt datasets back to sas datasets and I am ending with errors.

%xpt2loc(libref=work, memlist=Thisisalongdatasetname, filespec='c:\trans.v9xpt' );

I susbstituted the above auto call code with %xpt2loc macro call and its not working. Any help on this issue. I have to convert 100 xpt datasets using the %xpt2loc macro.

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

Tom — Wed, 21 Apr 2021 19:53:32 GMT

Before using a macro to generate code (or any other code generation technique) make sure you know what code you want to generate. Show an example of a working call to %XPT2LOC() for one of your existing transport files.

From the header of the macro definition you probably only want to supply the LIBREF and the FILESPEC parameters.

%*-------------------------------------------------------------------*;
%* The xpt2loc macro is used to convert a transport file into local  *;
%* SAS data set representation. The parameters are:                  *;
%*                                                                   *;
%* libref=          indicates the libref where the members will be   *;
%*                  written. The default is WORK.                    *;
%* memlist=         indicates the list of members in the library     *;
%*                  that are to be converted. The default is that    *;
%*                  all members will be converted.                   *;
%* filespec=        gives a fileref (unquoted) or a file path        *;
%*                  (quoted) where the transport file resides        *;
%*                  written. There is no default.                    *;
%*                                                                   *;
%* This macro should be able to handle V5 transport files written by *;
%* the XPORT engine. It should also handle V8 extended transport     *;
%* files written by the companion loc2xpt macro.                     *;
%*-------------------------------------------------------------------*;

%macro xpt2loc(libref=work,memlist=_all_,filespec=);
...

Where are you getting the list of transport files? You cannot use the same method you used for making the list of datasets when going the other way.

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

venkatagopinath — Wed, 21 Apr 2021 20:14:47 GMT

Hi Tom, First I Thank you for your response. I tried to get the list of transport files using the same code. As you suggested I cant use the code the other way. Can you suggest how to get the list of xpt files or can you suggest how to convert all the xptfiles at one go. I used %xpt2loc macro to convert single xpt file to sas dataset.

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

venkatagopinath — Wed, 21 Apr 2021 20:27:47 GMT

to be more specific i used the below code to get the list of transport files and for converison. which is giving errors.

proc sql;
create table sdtmDomains as
select libname
,memname
from dictionary.tables
where libname eq 'SDTM'
order by memname;
quit;

data _null_;
set sdtmDomains end=eof;
call symput('domain_' || strip(put(_n_,2.))
,strip(lowcase(memname))
);
if eof then
call symput('domainCnt',strip(put(_n_,2.)));
run;

%macro xpt;
%do idx=1 %to &domainCnt;

filename xptfile "c:\public\sdtm\&&domain_&idx...xpt";

%xpt2loc(libref=sdtm, memlist= &&domain_&idx , filespec=xptfile);

%end;
%mend xpt;
%xpt

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

ballardw — Wed, 21 Apr 2021 20:36:14 GMT

I really doubt that transport files are going to appear in dictionary.tables. Since your stated goal is to transform the transport files to data sets how can they appear in the dictionary.tables, which contains information about data sets and views but not external files such as XPT. The only possible connection is if you have xpt files whose names sort of match existing data sets which seems most likely to only replace existing data sets.

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

venkatagopinath — Wed, 21 Apr 2021 21:23:20 GMT

Hi ballardw,

Thanks for your repsonse. I agree with you transport files are not displayed even when i write proc datasets statement. Can you give more explanation on the possible connection. do you mean my xpt files and sas datasets should have names?

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

ballardw — Wed, 21 Apr 2021 22:32:49 GMT

@venkatagopinath wrote:

Hi ballardw,

Thanks for your repsonse. I agree with you transport files are not displayed even when i write proc datasets statement. Can you give more explanation on the possible connection. do you mean my xpt files and sas datasets should have names?

XPT files are text files in a specific format so that different operating system versions of SAS could interchange data. Which was pretty critical 30 years ago when almost no network connectivity allowed machines to talk to each other. So a standard file format is read by any version of SAS and you can create a new data set in the local SAS version.

Your "driver" program to call the macro would have to get a list of XPT files. There are lots of examples on the forum using Filename with PIPE to use the operating system file list tools to get file names into a data set.

Here is just one to get CSV files.: https://communities.sas.com/t5/SAS-Programming/Importing-csv-files-from-local-folder-in-work/m-p/550034

you will want to read some of the discussion and make sure that your variable to hold the file names is long enough for the entire path you need to search and replace CSV with XPT.

With Windows you might want to use the /S switch on the DIR command to search for subfolders if your XPT files are in multiple folders under a specific starting named folder.

Then you would want to make sure the full filename, from the drive letter to the XPT is in file name variable to use with the macro.

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

Patrick — Wed, 21 Apr 2021 22:45:39 GMT

@venkatagopinath

If I understand right then you can make things work for a single .xpt. You now need just a way to implement so you can execute dynamically for a list of .xpt

The first step in your code attempts to create such a list of .xpt files for further processing. As you already understand .xpt files are from a SAS perspective external files and though not added to the SAS dictionary for tables.

Use any logic that list files in a folder. One common method is to use a filename pipe with dir (for Windows) or ls (for Unix/Linux) to create such a list. This approach requires system option XCMD set (default is NOXCMD).

If you have to deal with NOXCDM then there are also SAS code only ways to create a directory listing. You could use sample code from the SAS Docu found here as a starting point. ...or may-be some "Googling" brings up something that's even closer to what you need.

Re: Using an AUTO call macro %xpt2loc to convert datasets in a library

Tom — Thu, 22 Apr 2021 01:51:13 GMT

@venkatagopinath wrote:

Hi Tom, First I Thank you for your response. I tried to get the list of transport files using the same code. As you suggested I cant use the code the other way. Can you suggest how to get the list of xpt files or can you suggest how to convert all the xptfiles at one go. I used %xpt2loc macro to convert single xpt file to sas dataset.

Are you doing this task once? Then just copy and paste the filenames into the program editor and convert the list of names into macro calls.

Otherwise use any of the many methods you can find in answers to multiple questions here to build the list of filenames.

data files ;
  infile "dir c:\mydir\*.xpt /b" pipe truncover ;
  input filename $256.;
  filename = cats('c:\mydir\',filename);
run;
data files ;
  infile "ls -d /mydir/*.xpt" pipe truncover ;
  input filename $256.;
run;
data files;
  length filename $256 ;
  rc=filename('dir','c:\mydir\');
  did=dopen('dir');
  do i=1 to dnum(did);
    filename=cats('c:\mydir\',dread(did,i));
    if scan(filename,-1,'.')='xpt' then output;
  end;
  rc=dclose(did);
  rc=filename('dir');
  keep filename;
run;