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

[A_Kh]

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!

[PaigeMiller]

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.

[A_Kh]

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

[A_Kh]

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;

[PaigeMiller]

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.

[data_null__]

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

rename STBRTHDAT=STBRTHDT;

[ballardw]

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.

[A_Kh]

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 

[ballardw]

---

@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.

[A_Kh]

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

[ballardw · Accepted solution]

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

[A_Kh]

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! 

[Ksharp]

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)

[Ksharp]

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

[A_Kh]

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?