topic Re: xport transport file not extracting empty datasets (0 observations) properly in SAS Programming

xport transport file not extracting empty datasets (0 observations) properly

himamsa — Mon, 30 Aug 2021 11:25:00 GMT

i) In Linux box using SAS V9.4, I created 3 datasets which has one variable but no observations. Essentially, all datasets are empty.
ii) I used loc2xpt macro to create v8 xport file using above 3 sets.
iii) Then I moved this file to target computer (windows box) and used xpt2loc macro to extract above datasets.
iv) Now when I try to open these extracted datasets in SAS, xport file content is getting displayed as observation in first and second datasets. For 3rd dataset, obs are shown as 0 (which is expected).

Below is the code I used to create and extract transport file contents respectively.

libname source '/home/datasets';
%loc2xpt(libref=source,
filespec='/home/transportfiles/test.xpt',
FORMAT=V8)

libname source 'C:\Users\xxx\Downloads\datasets';
%xpt2loc(libref=source,
filespec='C:\Users\xxx\Downloads\test.xpt');

Can someone help me why first and second datasets obs are not empty. Is there a workaround for this? Thanks.

I attached zip file which contains the original transport file and snapshot of dataset.

Note: I opened these datasets manually at source before creating xport file and confirmed that all datasets observations are 0.

Additional info: Encoding: UTF-8, Source: Linux, Target: Windows

Re: xport transport file not extracting empty datasets (0 observations) properly

SASJedi — Mon, 30 Aug 2021 17:58:17 GMT

I recommend using PROC CPORT / PROC CIMPORT instead:

/* Create some empty data sets for testing */
data work.one
     work.two
     work.three;
   stop;
   length text $1 number 8;
run;

/* Prove tables exists with no rows */
proc sql;
select MEMNAME, NVAR, NLOBS
   from dictionary.tables
   where memname in ("ONE","TWO","THREE");
quit;

/* Export the empty datasets */
filename xport "c:\temp\my_transport_file.xpt";
proc cport library=work file=xport memtype=data;
run;
filename xport clear;

/* Clear out all datasets in the WORK library */
proc datasets library=work kill nolist nodetails;
run;

From the Results tab:

Member Name	Number of Variables	Number of Observations
ONE	2	0
THREE	2	0
TWO	2	0

Now, re-import the data:

/* Import the empty data sets */
filename import "c:\temp\my_transport_file.xpt";
proc cimport library=work file=import;
run;

/* Prove tables exists with no rows */
proc sql;
select MEMNAME, NVAR, NLOBS
   from dictionary.tables
   where memname in ("ONE","TWO","THREE");
quit;

From the Results tab:

Member Name	Number of Variables	Number of Observations
ONE	2	0
THREE	2	0
TWO	2	0

This should wor better for you.

Re: xport transport file not extracting empty datasets (0 observations) properly

himamsa — Mon, 30 Aug 2021 18:06:42 GMT

Hi, Thanks for the response. I have already tried for cport format and verified that it is working fine. But I am looking to achieve the same for xport format in V8.

Re: xport transport file not extracting empty datasets (0 observations) properly

Kurt_Bremser — Tue, 31 Aug 2021 07:51:46 GMT

Tested this with

proc datasets lib=work kill nolist;
quit;

data d1;
x = 0;
stop;
run;

data d2;
x = 0;
stop;
run;

data d3;
x = 0;
stop;
run;

%loc2xpt(libref=work,
filespec='~/test.xpt',
FORMAT=V8);

proc datasets lib=work kill nolist;
quit;

%xpt2loc(libref=work,
filespec='~/test.xpt');

on ODA, and the import created wrong results, D1 has 160 and D2 80 observations with bogus values; only D3 imported correctly with zero observations. I repeated the action and omitted the FORMAT= parameter, same result.

In my experience, simple PROC CPORT / PROC CIMPORT works so well, there's no need for anything else:

proc datasets lib=work kill nolist;
quit;

data d1;
x = 0;
stop;
run;

data d2;
x = 0;
stop;
run;

data d3;
x = 0;
stop;
run;

proc cport lib=work file='~/test.xpt' memtype=data;
run;

proc datasets lib=work kill nolist;
quit;

proc cimport file='~/test.xpt' lib=work;
run;

works like a charm, and does not flood the log with lots of NOTEs.

Re: xport transport file not extracting empty datasets (0 observations) properly

Kurt_Bremser — Tue, 31 Aug 2021 10:19:42 GMT

Similarly, if you want to use the XPORT engine to go to an earlier SAS release, doing it yourself is straight forward:

libname xpt xport "~/test.xpt";

proc copy in=work out=xpt memtype=data;
select d:;
run;

libname xpt clear;

libname xpt xport "~/test.xpt";

proc datasets lib=work kill nolist;
quit;

proc copy in=xpt out=work;
run;

libname xpt clear;

Re: xport transport file not extracting empty datasets (0 observations) properly

Tom — Tue, 31 Aug 2021 11:23:53 GMT

What happens if you create a fileref that points to the XPORT file instead of trying to pass the macro a physical filename?

That worked for me.

libname source 'C:\Users\xxx\Downloads\datasets';
filename xpt 'C:\Users\xxx\Downloads\test.xpt';
%xpt2loc(libref=source,filespec=xpt);

Re: xport transport file not extracting empty datasets (0 observations) properly

Ksharp — Tue, 31 Aug 2021 12:52:52 GMT

The file generated by PROC CPORT is not a real XPT file . FDA would not accept it .

Re: xport transport file not extracting empty datasets (0 observations) properly

himamsa — Tue, 31 Aug 2021 13:39:40 GMT

Hi Tom, this also didnt work for me. Maybe you generated for v5 format. v5 format works fine but not v8.
I am looking to create v8 xport file as my requirement is I have few non empty datasets whose variable length is greater than 32. Thanks.

Re: xport transport file not extracting empty datasets (0 observations) properly

Tom — Tue, 31 Aug 2021 14:30:17 GMT

I am lost in what you are trying to test.

I was able to load that XPT file into the WORK library using the SAS supplied macro.

There were three datasets.

NOTE: The data set WORK.FORM_04 has 1280 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The infile XPT is:
      Filename=C:\downloads\xpt_emptydataset\xpt_emptydataset\test.xpt,
      RECFM=N,LRECL=32767,File Size (bytes)=1920,
      Last Modified=30Aug2021:06:23:30,
      Create Time=31Aug2021:07:14:46

NOTE: The data set WORK.LB_01 has 640 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The infile XPT is:
      Filename=C:\downloads\xpt_emptydataset\xpt_emptydataset\test.xpt,
      RECFM=N,LRECL=32767,File Size (bytes)=1920,
      Last Modified=30Aug2021:06:23:30,
      Create Time=31Aug2021:07:14:46

NOTE: The data set WORK.LB_01_1 has 0 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

What is happening differently on your machine?

Re: xport transport file not extracting empty datasets (0 observations) properly

Kurt_Bremser — Tue, 31 Aug 2021 17:01:40 GMT

Since, from the documentation, the datasets should be unchanged after export/import with these macros, and the test on ODA that I did clearly contradicts this, I suggest you bring this to the attention of SAS technical support, as this looks like a (IMHO serious) bug to me.

Re: xport transport file not extracting empty datasets (0 observations) properly

himamsa — Tue, 31 Aug 2021 17:18:05 GMT

At source computer I have 3 empty datasets (1 variable and 0 observations). Then I created xport file in V8 format using loc2xpt.
And then at target computer, I extracted these 3 datasets from this xport file using xpt2loc.

Expected: Now when I view the extracted datasets, all of these should have been empty.
Actual: But FORM_04 and LB_01 are not turning out be empty as per logs below. These contains some observations which is wrong. So, I am trying to figure out the solution for this.

NOTE: The data set WORK.FORM_04 has 1280 observations and 1 variables.
NOTE: The data set WORK.LB_01 has 640 observations and 1 variables.

Re: xport transport file not extracting empty datasets (0 observations) properly

himamsa — Tue, 31 Aug 2021 17:21:34 GMT

I am a novice in SAS so I am exploring options to see if there is a way to fix this before I contact SAS support.

Re: xport transport file not extracting empty datasets (0 observations) properly

Tom — Tue, 31 Aug 2021 17:54:50 GMT

So it looks like the macro has consumed the header and other records in the XPT file as if they were data lines for the first two files. The last file is generated with Zero observations because there are no trailing records to confuse it.

154   data _null_;
155     set form_04 (obs=20);
156     put nodata $1. @;
157   run;

HEADER RECORD*******
NOTE: There were 20 observations read from the data set WORK.FORM_04.

Until you get a fix the work around is to write only one dataset per file.

Or just make sure your datasets conform to SAS Version 5 standards (8 character names, 200 maximum character string, 40 character labels) and use the real XPORT engine instead of the SAS macros. Example code:

data work.one
     work.two
     work.three;
   stop;
   length text $1 number 8;
run;

filename new temp ;
libname xpt xport "%sysfunc(pathname(new))";
proc copy inlib=work outlib=xpt;
  select one two three;
run;

proc copy inlib=xpt out=work;
run;

PROC COPY correctly generates dataset with zero observations.

256   proc copy inlib=xpt out=work;
257   run;

NOTE: Input library XPT is sequential.
NOTE: Copying XPT.ONE to WORK.ONE (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 0 observations read from the data set XPT.ONE.
NOTE: The data set WORK.ONE has 0 observations and 2 variables.
NOTE: Copying XPT.TWO to WORK.TWO (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 0 observations read from the data set XPT.TWO.
NOTE: The data set WORK.TWO has 0 observations and 2 variables.
NOTE: Copying XPT.THREE to WORK.THREE (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 0 observations read from the data set XPT.THREE.
NOTE: The data set WORK.THREE has 0 observations and 2 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds

But if you try to read that demonstrably valid XPORT file using the macro instead the same thing happens as with your example.

%xpt2loc(libref=work,filespec=xpt);

NOTE: The data set WORK.ONE has 178 observations and 2 variables.
NOTE: The data set WORK.TWO has 89 observations and 2 variables.
NOTE: The data set WORK.THREE has 0 observations and 2 variables.

Re: xport transport file not extracting empty datasets (0 observations) properly

Kurt_Bremser — Tue, 31 Aug 2021 18:07:42 GMT

@himamsa wrote:
I am a novice in SAS so I am exploring options to see if there is a way to fix this before I contact SAS support.

This is a bug in a SAS-supplied macro. The proper people to deal with bugs in SAS software sit at SAS TS. Being a novice does not mean you can't stumble across a bug.

Re: xport transport file not extracting empty datasets (0 observations) properly

Tom — Wed, 01 Sep 2021 11:40:55 GMT

Here is a method to split the XPT file into individual files.

data _null_;
  if eof then call symputx('nmembers',memnum);
  infile xpt lrecl=80 recfm=f end=eof;
  if _n_=1 then do;
    input line1 $char80. / line2 $char80. / line3 $char80. ;
    retain line1-line3;
  end;
  input ;
  if _infile_=:'HEADER RECORD*******MEM' then do;
    memnum+1;
    lineno=0;
    filename=cats(pathname('work'),'/temp',memnum,'.xpt');
  end;
  file out lrecl=80 recfm=f filevar=filename;
  if lineno=0 then put line1 $char80. / line2 $char80. / line3 $char80. ;
  lineno+1;
  put _infile_;
run;
%put &=nmembers;

Re: xport transport file not extracting empty datasets (0 observations) properly

SASJedi — Fri, 03 Sep 2021 14:16:40 GMT

Thanks for the explanation!

Re: xport transport file not extracting empty datasets (0 observations) properly

JodyC_SAS — Fri, 10 Sep 2021 21:28:03 GMT

@himamsa I have reported this bug to Development. @Tom Thanks for reporting it to SAS Technical Support.