XPT2LOC Producing Empty Dataset (V8)

[craig159753]

Hi, 

I have a version 8 transport file (xptv8) and I am using the XPT2LOC autocall macro to convert the transport file into a SAS dataset. Now, previously I was calling this XPT2LOC macro inside SAS Enterprise Guide 8.3 on the SAS On Demand for Academics (ODA) server, which is running SAS 9.4 M8. This was working fine.

However, since then, I have setup my own server, also running SAS 9.4 M8 but running a slightly newer version of SAS Enterprise Guide, version 8.4 (not that it likely matters but worth mentioning). Now on this new system, the code which ran without issues on the ODA server, is still running without any issues appearing in the SAS log but the dataset being output is empty.

Below is the code I am running on both systems:

options mprint mlogic symbolgen;
libname source 'C:\Users\myuser\Documents';

%* Convert the XPT file to a SAS dataset;
%xpt2loc(
  libref=source, 
  memlist=_all_, 
  filespec='C:\Users\myuser\Documents\ae.xptv8');

Now, I have done some testing and looking at the logs for both the system on the ODA server and the system on my new server, and I noticed one minor detail,. Within the XPT2LOC macro there is a dataset which reads in the XPT file using an INFILE statement. This data step processes the variables, defines their length, applies labels, etc. and then it outputs the dataset. The code from my new system (see sas_log_1.txt attached), the data step ends like this 

  stop;
run;

But in the ODA system (see sas_log_2.txt attached) the same data step ends like this

  if _n_=29 then stop;
run;

This 29 is the number of records / observations in my XPT file. Now, this line is determined by the macro variable OBS_MEM0001, which is created in the first and very large data step. In the old ODA system this macro variable revolves to 29, but in my new system it resolves to 0. I believe something is making my new system not see the number of records in my XPT file and I do not know why. Is this a bug?

I have attached the xpt I am working with and the SAS logs from my new system. Any insight would be greatly appreciated. 

[Tom]

Did you forget to unzip the transport file before trying to read it with %XPT2LOC?

Look at this other post from today about dealing with zipped transport files.

https://communities.sas.com/t5/New-SAS-User/ERROR-File-TRANSPRT-ALL-is-not-a-SAS-data-set/m-p/948375...

[craig159753]

Yes, the file is unzipped (the only reason it is attached as a zip here is because the formats xpt and xptv8 are not supported on this website).

[Patrick]

Thank you for your detailed problem description and provisioning of logs and the transport file.

I can replicate what you describe and it looks to me that a change introduced latest with SAS94M8 to xpt2loc.sas is causing this.

I found a different (likely older) version of the macro code under https://support.sas.com/kb/46/addl/fusion_46944_9_xpt2loc.sas.txt and once I've executed this code to compile the macro into Work your code returned the desired result.

filename urlfile url 'https://support.sas.com/kb/46/addl/fusion_46944_9_xpt2loc.sas.txt';
%include urlfile;

options mprint mlogic symbolgen;

%* Convert the XPT file to a SAS dataset;
%xpt2loc(
  libref=work, 
  memlist=_all_, 
  filespec='C:\temp\ae.xptv8');

proc print data=work.ae;
run;

I've tested both with a local Windows SAS9.4M8 version and a current Viya 4 Azure cloud version and in both environments the out-of-the-box macro code returned a zero row table.

I'm going to raise this with SAS Tech Support.

Below the differences between the code version under https://support.sas.com/kb/46/addl/fusion_46944_9_xpt2loc.sas.txt (left side) what's installed in my Windows environment (right side).

[craig159753]

Hi Patrick,

Thank you after further investigation, I came to the same resolution as you, my SAS ODA server's source code for XPT2LOC was ever so slightly different to my server's source code for XPT2LOC.

My Server's Source Code

process_data_record:;
  file metadata;
  if v6comp or nobs > 0 then do;
    file metadata;
    put '/* INPUT STATEMENT HERE */';
    file inptstmt;
    put '/* IN DATA_RECORD */';
    put '@@;';
    if v6comp then put 'if not &obs_mem' memnum z4. ' then stop;';
    put '     output; if _n_=&obs_mem' memnum z4. ' then stop;';
    put 'run;';
  end;
  else do;
    file metadata;
    put '/* INPUT STATEMENT HERE */';
    put 'run;';
    file inptstmt;
    put ';stop;';
    put 'run;';
  end;
  firstobs=recnum+1;
  retain firstobs;
  return;
run;

The ODA Server's Source Code

process_data_record:;
  file metadata;
  put '/* INPUT STATEMENT HERE */';
  file inptstmt;
  put '@@;';
  put '     output; if _n_=&obs_mem' memnum z4. ' then stop;';
  put 'run;';
  firstobs=recnum+1;
  retain firstobs;
  return;
run;

The quickest and easiest solution is to edit our XPT2LOC.sas macro and replace that single line:

put ';stop;';

With this line:

put ';if _n_=&obs_mem' memnum z4. ' then stop;';

But I believe the way NOBS is being calculated in the data step above is incorrect and the empty dataset code is executing by mistake.

To help, I have attached the three files created by XPT2LOC macro - INPTSTMT, METADATA and SASCODE.

[Patrick · Accepted solution]

@craig159753 

Just use the code version under the URL I've provided. This is a link under support.sas.com and though code that's fully supported.

I've already raised this with SAS Tech Support so hopefully this will get fully investigated and rectified (i.e. via a patch).

[craig159753]

Thanks for doing this, I will accept this as the solution, but once you get an update about the ticket and a patch, could you let me know please.

[Patrick]

@craig159753 

Do you know how transport file ae.xptv8 has been created? Was this via SAS some 3rd party software?

Reason I'm asking: 

If 3rd party created then the header format of your V8 transport file was eventually never fully compliant with the SAS spec (whether documented or not) and things might just have worked by "luck". 

[Tom]

In the original V5 format the observation count is not stored in the member record. So the reader of the XPORT file had to figure out how many observations there were by just seeing how many data records were in the file.

The original version of XPT2LOC macro had a BUG that prevented it from properly handling zero obs datasets when reading from a V5 XPORT file that contained multiple members.   When the zero obs dataset was not the last one in the XPORT file instead of stopping when the next dataset started it would read to the end of the file.  So it would load the metadata and data records of the all of the following files into the dataset that was supposed to be zero obs.  

Apparently their attempt to fix this is what caused the error you are seeing.

My XPORT2SAS macro handles those zero obs datasets properly.

https://github.com/sasutils/macros/blob/master/xport2sas.sas

[Chevell_sas]

This is also documented in the V8 layout. A SAS Note will also be added.

SAS Help Center: Record Layout for a SAS Version 8 or 9 Data Set in SAS Transport Format

Observation header: The "0" at the end of the header specifies the number of observations in each data set for the OBSV8 header. In this example, there are 0 observations.

HEADER RECORD*******OBSV8 HEADER RECORD!!!!!!!000000000000000000000000
000000

Here is another header example. The "1" at the end of the header specifies the number of observations in each data set for the OBSV8 header.

HEADER RECORD*******OBSV8 HEADER RECORD!!!!!!! 1

[Tom]

---

@Chevell_sas wrote:

This is also documented in the V8 layout. A SAS Note will also be added.

SAS Help Center: Record Layout for a SAS Version 8 or 9 Data Set in SAS Transport Format

Observation header: The "0" at the end of the header specifies the number of observations in each data set for the OBSV8 header. In this example, there are 0 observations.

HEADER RECORD*******OBSV8 HEADER RECORD!!!!!!!000000000000000000000000
000000

Here is another header example. The "1" at the end of the header specifies the number of observations in each data set for the OBSV8 header.

HEADER RECORD*******OBSV8 HEADER RECORD!!!!!!! 1

---

It is not the specifications that are at issue.  It is the coding of the macros that SAS has created.

But perhaps the documentation could be corrected also.  When the field in the OBS header record that could hold the number of observations is filled with zero digits (hexcode '30'x) it means the number of OBS was not know. 

When the OBS value is known then field in the OBS header will have a digit string padded with spaces ('20'x) in positions that are not used.   Like in your example of the record that indicates 1 observation.

When the OBS value is not known then the code that is reading the file to create the program that reads the data needs to count the number of 80 byte records of data exist after the OBS header and before the start of the next member so that it can determine how many observations to read.