Storing SAS data (including table structure) in a single flat file

Question

I need to convert SAS data tables into flat files (or "ASCII files" as they were called once, as opposed to binary files). And only 1 flat file for each original SAS table. The challenging thing is that I want the flat file to contain some structural information of the original SAS table also, specifically:

• Variable/Column name
• Variable/Column label
• Variable/Column type
• Variable/Column length
• Variable/Column format
• Variable/Column informat

Additional information:

• I will only need to convert small data (< 100 obs).
• Performance is not an issue (within reasonable limits).
• The flat file should form a basis for recreating the original SAS table, I don't need to be able to use the file directly as a table in DATA or PROC steps.

The standard SAS tables, transport files, XPORT files, etc are all binary format files, and the standard XML table format in SAS and CSV-files don't preserve table structure. So obviously these options don't help.

What is my best option?

Answer 1

If you're only going to use the data in SAS, then you can just use PROC COPY to make transport files:

http://www.usc.edu/isd/doc/statistics/sas/sastransport/

Answer 2

I'm not aware of any easy solutions.

Possibly:

1. Use PROC EXPORT to produce CSV file with the data in it.
2. Use PROC DATASETS with ODS to produce a dataset with the names, types, etc.
3. Produce another CSV file for this dataset.

Now you've got your ASCII description of the table (spread over two CSV files). Reversing the process would be more tricky. Basically you'd have to read in the description data set, then use CALL SYMPUT in a loop to create a bunch of macro variables with the information in them, then use your macro variables to build a PROC IMPORT for the CSV file...

Answer 3

1. Create the code to export the table to text (this is straightforward, just google it or look at 'The Little SAS Book' if you have a copy).

2. Then append the 'meta' info from sashelp.vcolumn, which is where sas stores information (meta data) about sas datasets. It's a sas table itself, so you could do a proc sql union operation to join it with the actual columns that this table describes (though you will need to do a transpose type operation because the meta data about the columns is in rows, not columns).

You're not being completely specific about how you want to see the meta data in the text file, so that's as far as I can go.

Answer 4

proc sql's describe syntax might be handy to get the metadata portion, including lengths, types, formats, indexes etc...

Code:

proc sql;
describe table sashelp.class;
quit;

Log:

NOTE: SQL table SASHELP.CLASS was created like:

create table SASHELP.CLASS( bufsize=4096 )
  (
   Name char(8),
   Sex char(1),
   Age num,
   Height num,
   Weight num
  );

Answer 5

BTW - you haven't said why you need to do this. In this case, there is no good reason (there might be a compelling reason, such as somebody with power saying 'do it, or be fired', but there's no good reason).

I'd give up the idea of merging the metadata and data in each file, unless there's some incredibly strong reason to do so. Go with exporting the metadata for data set A into a file called metadata_A; this will result in paired files. Anybody looking to use those files in a a database program or statistical program would have a clearly-labeled metadata file to work with.

Answer 6

With SAS 9.2, you can create an XML file from a data set and the XML contains variable/column metadata, like format, label, etc... See the section of the SAS 9.2 XML LIBNAME Engine: User's Guide titled "Using the XML Engine to Transport SAS Data Sets across Operating Environments". A link to it is here:

http://support.sas.com/documentation/cdl/en/engxml/61740/HTML/default/a002594382.htm

Here's a section of code from the manual that shows using the XML92 libname engine and PROC COPY to create the XML:

libname myfiles 'SAS-library';
libname trans xml92 'XML-document' xmltype=export;
proc copy in=myfiles out=trans;
   select class;
run;

In SAS 9.1.3, you may have to create a custom tagset to get the same operation. SAS Technical Support ([email protected]) may be able to offer some help.