topic Re: PROC COPY/EXPORT engine limitations for transport files (.xpt) in SAS Programming

PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Thu, 07 Apr 2022 14:32:01 GMT

Hi Community,

While exporting sas dataset into .xpt transport file I'm getting error for variable names longer than 8 characters, like ERROR: The value xyz is not a valid SAS name.

There is a system suggestion to apply options VALIDVARNAME=v6, in order to allow auto truncation of variable length up to 8. But this is not the expectation.
So i'm wondering if there is any techniques that allows to export longer sas variable names into .xpt file using PROC COPY.

Thanks a lot!

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

PaigeMiller — Thu, 07 Apr 2022 14:37:47 GMT

Please show us the ENTIRE log from this run of PROC COPY or PROC EXPORT (that's every single line, every single character in the log for this PROC COPY or PROC EXPORT). Copy the log as text and paste it into the window that appears when you click on the </> icon.

From now on, you need to do this every time you have an error in the log. Do not show us ERROR message detached from the rest of the log.

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

ballardw — Thu, 07 Apr 2022 14:49:44 GMT

Are you sure you are using EXPORT? For old file format transport files I would expect XPORT, they are different procedures.

I suggest that you look into Proc CPORT (and CIMPORT on the other side)

From the documentation of XPORT:

XPORT Engine Limitations

Using the XPORT engine has these limitations:

The XPORT engine supports only members of type DATA. It does not support members of type CATALOG or VIEW.

The XPORT engine supports a feature set that is compatible with SAS 6. The XPORT engine cannot support SAS 9 features, such as long variable names. Warning or error messages report limitations that are encountered during the transport operation. For details about typical error messages and recovery actions, see File library.member.DATA has too long a member name for the XPORT engine .

The XPORT engine with PROC COPY does not support the transport of any type of view or MDDB.

So using XPORT will not allow the longer variable names. So if you want long variable names CPORT is the procedure you need.

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Thu, 07 Apr 2022 14:54:06 GMT

NOTE: Copying RESULT.xyz to XPT.xyz (memtype=DATA).
NOTE: There were 0 observations read from the data set RESULT.xyz.
NOTE: The data set XPT.xyz has 0 observations and 0 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


ERROR: The value STBRTHDAT is not a valid SAS name.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set RESULT.xyz may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
WARNING: Data set RESULT.xyz was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

@PaigeMiller Please see the log above, thank you for the tips, I couldn't figure out how to paste codes there

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Thu, 07 Apr 2022 14:55:45 GMT

Using this code:

options VALIDVARNAME=V6;
%macro export;
	%do i= 1 %to 8;
		libname xpt xport "&output/&&dataset&i...xpt";
		proc copy in=result out=xpt; 
			select &&dataset&i/memtype=data;
		run;
	%end;
%mend export;

%export;

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

PaigeMiller — Thu, 07 Apr 2022 14:55:59 GMT

This is not the ENTIRE log that I asked for. Where is the code that appears in the log? We need to see that in order to understand what was wrong.

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Thu, 07 Apr 2022 15:00:19 GMT

Hi @ballardw ,
Using XPORT engine with below code:

options VALIDVARNAME=V6;
%macro export;
	%do i= 1 %to 8;
		libname xpt xport "&output/&&dataset&i...xpt";
		proc copy in=result out=xpt; 
			select &&dataset&i/memtype=data;
		run;
	%end;
%mend export;

%export;

But Options VALIDVARNAME=v6 I added after the system suggestion. Even this option did not allow to export all files in the macro...
The purpose is not applying VALIDVARNAME=v6.
I'll try with CIMPORT now

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

ballardw — Thu, 07 Apr 2022 15:02:42 GMT

@A_Kh wrote:

Hi @ballardw ,
Using XPORT engine with below code:
options VALIDVARNAME=V6;
%macro export;
	%do i= 1 %to 8;
		libname xpt xport "&output/&&dataset&i...xpt";
		proc copy in=result out=xpt; 
			select &&dataset&i/memtype=data;
		run;
	%end;
%mend export;

%export;
But Options VALIDVARNAME=v6 I added after the system suggestion. Even this option did not allow to export all files in the macro...
The purpose is not applying VALIDVARNAME=v6.
I'll try with CIMPORT now

CPORT to export the data/create transport file CIMPORT on the receiving end to turn the transport file back to SAS data sets or catalogs.

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Thu, 07 Apr 2022 15:12:52 GMT

CPORT did not work either:

Submitted:

%macro export;
	%do i= 1 %to 8;
		FILENAME xpt "&output/&&dataset&i...xpt";
		PROC CPORT LIBRARY=result FILE=xpt; 
			select &&dataset&i/memtype=data;
		run;
	%end;
%mend export;

%export;

Log:

1319  %macro export;
1320      %do i= 1 %to 8;
1321          FILENAME xpt "&output/&&dataset&i...xpt";
1322          PROC CPORT LIBRARY=result FILE=xpt;
1323              select &&dataset&i/memtype=data;
1324          run;
1325      %end;
1326  %mend export;
1327
1328  %export;


NOTE: PROC CPORT begins to transport data set RESULT.xyz
ERROR: The value xyBRTHDAT is not a valid SAS name.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

data_null__ — Thu, 07 Apr 2022 15:32:31 GMT

Why don't you just RENAME to a valid V6 name.

rename STBRTHDAT=STBRTHDT;

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

ballardw — Thu, 07 Apr 2022 15:47:44 GMT

It is time to stop hiding the names of the data sets involved. I am starting to suspect the issue is not a variable but data set name.

Create a statement exporting Result.xyz (and if you have been changing/hiding actual name stop) with NO macro variables, and show the log from running that.

OR alternatively, set Options Mprint; before running the macro and show the all the log with the generated code from the log.

Also check syntax. Single data set with cport looks like:

libname source 'sas-library';
filename tranfile 'transport-file'    ;

proc cport data=source.datasetname file=tranfile;
run;

Also you can sent all data sets in a library to a single file.

BTW, I think that you may have one too many . in the Filename unless you actually want the file to end in ..xpt

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Thu, 07 Apr 2022 16:22:13 GMT

Hi @ballardw ,

Syntax tips for PROC CPORT was helpful, looks like the issue was exactly that one. No name issue, it was just short names similar to "xyz".

CPORT created all 8 files successfully.

Below is the run log:

 %macro export;
1436      %do i= 1 %to 8;
1437          filename &&dataset&i "&&dataset&i...xpt";
1438          proc cport data=result.&&dataset&i file=&&dataset&i;
1439          run;
1440      %end;
1441  %mend export;
1442
1443  %export;


NOTE: PROC CPORT begins to transport data set RESULT.xy
NOTE: The data set contains 28 variables and 127 observations.
      Logical record length is 728.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.05 seconds
      cpu time            0.01 seconds




NOTE: PROC CPORT begins to transport data set RESULT.xyz
NOTE: The data set contains 31 variables and 1224 observations.
      Logical record length is 928.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.08 seconds
      cpu time            0.03 seconds




NOTE: PROC CPORT begins to transport data set RESULT.xx
NOTE: The data set contains 26 variables and 554 observations.
      Logical record length is 744.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds




NOTE: PROC CPORT begins to transport data set RESULT.yy
NOTE: The data set contains 19 variables and 23 observations.
      Logical record length is 1440.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds




NOTE: PROC CPORT begins to transport data set RESULT.xxyyzz
NOTE: The data set contains 13 variables and 3 observations.
      Logical record length is 375.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds




NOTE: PROC CPORT begins to transport data set RESULT.yyxxzz
NOTE: The data set contains 13 variables and 239 observations.
      Logical record length is 375.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds




NOTE: PROC CPORT begins to transport data set RESULT.xyzxyz
NOTE: The data set contains 13 variables and 6 observations.
      Logical record length is 375.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds




NOTE: PROC CPORT begins to transport data set RESULT.zz
NOTE: The data set contains 14 variables and 59 observations.
      Logical record length is 248.
NOTE: PROCEDURE CPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

Thank you so much!
I appreciate all from SAS Community who involved in this conversation for their time and input!

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

Ksharp — Fri, 08 Apr 2022 12:48:17 GMT

By the way. the XPT file created by PROC CPORT is not real XPT file ,FDA would not accept it . Try using V8 version XPT file.

filename xpt  'c:\temp\XPT\all.xpt'; /*指定XPT文件的路径*/ 
libname sas v9    'c:\temp\SAS';        /*指定SAS数据集的路径*/ 

/*将sas库中所有的数据集转成一个xpt文件*/
%loc2xpt(libref=sas,filespec=xpt)

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

Ksharp — Fri, 08 Apr 2022 12:49:12 GMT

https://support.sas.com/kb/46/944.html

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Mon, 11 Apr 2022 20:48:15 GMT

Hi @Ksharp,

Thank you for sharing the information. I couldn't create .xpt using the V8 version due to syntax issiues. I use the server SAS, not installed in my laptop c disk.

Would you please give me the syntax for converting following dataset into .xpt using V8 xport engine?
dataset= "xyz.7bdat" need to be converted "xyz.xpt"

input_folder = "Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Input"

output_folder = "Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Results"

Used the below syntax which did not work.

FILENAME xpt "&output\xyz.xpt"; *output path and output file name;
LIBNAME SAS V9 "c:\temp\SAS";
%loc2xpt(libref=sas,filespec=xpt);

PROC COPY IN=result OUT=XPT;  *result= Input_folder;
	SELECTxyz/MEMTYPE=DATA;
RUN;

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

Ksharp — Tue, 12 Apr 2022 11:59:33 GMT

filename xpt 'Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Results\xyz.xpt';
libname sas v9 'Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Input';

%loc2xpt(libref=sas,filespec=xpt)

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

A_Kh — Tue, 12 Apr 2022 15:54:05 GMT

Hi @Ksharp ,

Thank you for providing the syntax!
I could create .xpt files, however couldn't convert them back to sas datasets using PROC COPY.

Besides, this way works only when creating a single dataset from all files in the INPUT folder. As I had 8 datasets to be transported separately, I had to process each file individually (without using do/loop as it was just testing).

Would you have more tips on how to improve the above experience?

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

Tom — Tue, 12 Apr 2022 16:06:14 GMT

SAS created two complementary macros for dealing with the extended xport format files. %XPT2LOC() and %LOC2XPT().

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/movefile/p1x39mw5fqqnyln1a08o7tomv47e.htm

The both support copying multiple datasets into/out of a single transport file. Just leave off the optional MEMLIST parameter and it will copy all members from the libref into the transport file. Or all members from the transport file to the libref.

PS Be careful with empty datasets as there was (still is?) a bug in %XPT2LOC() where it was unable to notice when an empty file ended which caused it to read the information about the next file as data for the file that should have had zero observations.

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

Ksharp — Wed, 13 Apr 2022 12:01:24 GMT

Follow the @Tom suggestion . There is another macro can do this . Same as

filename xpt 'Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Results\xyz.xpt';
libname sas v9 'Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Input';

%xpt2loc(libref=sas,filespec=xpt)