Help using Base SAS procedures

exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Hi,

I would like to transfer some datasets to one of our clients. I am using SAS 9.4 TSL1M1 under Windows7 Professional 64bit OS and the datasets contain long variable names, long labels and long string value (more than 2000 characters) which I can not use XPORT engine. I thought using XML engine might be an option. When I use the following codes and I got an note in log: "NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.". Is there a way to export datasets to an xml file with those attributes? Thanks in advance!

libname xmlout xmlv2 'c:\temp\test.xml';

data xmlout.central;

  set central;

run;

Helen


Accepted Solutions
Solution
‎02-04-2015 03:59 PM
Occasional Contributor
Posts: 7

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Thanks for all your help. Back to my first post, I think I find a way to add dataset label, variable label and formats via XML mapper.

step 1: use the following statements to create a xml file:

libname testfile xmlv2 'c:\temp\testing.xml';

proc copy in=work out=testfile;

   select local central;

run;

step2: open the above xml(testing.xml) in XML mapper, manually create a map file (testing.map) via drag and drop method and modify label, change data type, formats etc in the property tabs.

Send both xml and map files to recipient. He or she can use the following statements to restore 'local' and 'central' datasets:

filename mapfile  'c:\temp\testing.map';

filename testfile 'c:\temp\testing.xml';

libname testfile xmlv2 xmlmap=mapfile ;

proc copy in=testfile out=work;

run;

If we have many datasets or many variables need to be transfered, we can use PROC CONTENTS to get meta data, and use DATA step to create a xml mapping file since the structure of map file is very simple. I will give a try when I have a chance. Thanks again!

Helen

View solution in original post


All Replies
Super User
Posts: 10,538

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Would Proc CPORT work?

Occasional Contributor
Posts: 7

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

I don't think PROC CPORT would work since my version SAS is 9.4 and our client is lower (no more than 9.2).

Super User
Posts: 10,538

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

If your client can't receive the longer variable names and such from CPORT then they probably can't support them in general and the effort may be moot. Unless your client is very difficult to work with I would suggest making a small example data set that would test the characteristics and then try using CPORT and see if they can CIMPORT it without loosing anything. Just make sure to have the appropriate VALIDVARNAME and VALEDMEMNAME options set on both sides.

Occasional Contributor
Posts: 7

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Unfortunately I do have longer variable label and might have long values as time goes on. This is weekly data transfer and will go on for a while. And this is not just for one of our clients, I have similar situations for other clients too. I am hoping that there is a general solution for data transfer across different OS and different SAS version (especially highter version to lower version like 9.4 to 9.2) without losing any attributes for datasets. Any advices are much appreciated.

Super User
Posts: 10,538

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Long values as long as it is less than 32,000 shouldn't be an issue. How long are your variable names and labels?

9.2 has a label length limitation of 256. Are you exceeding that?

9.2 Variable names are limited to 32 characters.

The real issue with names would be the use of spaces and such in the variable names as I suspect many shops don't like to mess with the VALIDVARNAME=Any and having to using the resulting 'name of this var'n syntax.

Super User
Super User
Posts: 7,417

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

If your labels are getting long, then consider making them part of the data rather than the header information.  Column header labels longer than a page width are pretty useless as they wont display properly, or be useable in printed form.  Depending on your area, there may be other considerations, for instance we are required to provide SAS version 5 XPT files which obviously wouldn't allow long labels/long variable names.

Super User
Super User
Posts: 6,502

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Perhaps you just need to send the data as a program and a data file?

If the data format is not changing then for each re-send you just need to send the data file.

Making a data file is trivial in SAS.

data _null_;

  set mydata ;

  file 'myfile.csv' dsd lrecl=32000 ;

  put (_all_) (Smiley Happy;

run;

Runs very quickly also.

Occasional Contributor
Posts: 7

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

'.csv' file will lose some features which our clients would like to keep when restore the transfer file to SAS datasets like values with leading zeros, dataset label, variable label etc. Anyway I think the only choice I have is either using PROC CPORT for SAS version 9 or XPORT engine for SAS 8 or lower. I am new to XML and thought XML engine might be a general solution for all SAS version. I guess I was wrong. XML engine can not keep SAS dataset attributes like dataset label, variable label and format etc. Please let me know if my understanding of XML engine is wrong. Thanks for all your help.

Super User
Super User
Posts: 7,417

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Well, on your response to Tom on the CSV (which is probably the smallest - file size), it shouldn't matter if the data does not contain leading zeroes, that should be handled by the import program, to clarify, SAS does not store the leading zeroes either it is an applied format (e.g. zX.) which makes it look like it does, or its a text field.  CSV is pretty standard in most areas of data transfer and widely accepted.

With regards to the XML engine, well not used it so can't help there, however what I would say is that an XML file is but a simple text file with some structural and definition tags.  Hence there is no reason why you cannot write either a datastep with put statements, or even a tagset, to create any type of XML structure you want.

What are the requirements exactly, are the specified in a document?

Oh, just to add, there are specific reasons for choosing a format over another as they all have limitations:

Text:

CSV - possibly the smallest file size text data transfer possible without compression techniques.  Widely known and used.  Can be streamed across web sites.  Doesn't have any information about its structure natively though, so accompanying documentation is necessary.  Also cross platform.

Json - small, but not CSV small, not XML big either.  Has some descriptive information, but not as much as XML.  Cross platform.

XML - Most verbose.  Easily human readable.  Needs no further documentation (normally).  Cross platform.  Widely used.

Binary

Dataset - small size, fast SAS access.  Proprietary format so not human readable.  Needs software to read/port to other platforms.

XPT - Now open source.  Some places require this format.  Has strong limitations.

Super User
Super User
Posts: 6,502

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

The PROGRAM that reads the data would take care of that. 

Is the structure fixed? If so then just write the program once.

If not then use Metadata such as output of PROC CONTENTS to generate the program.

Solution
‎02-04-2015 03:59 PM
Occasional Contributor
Posts: 7

Re: exporting datasets via xml engine issue: NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Thanks for all your help. Back to my first post, I think I find a way to add dataset label, variable label and formats via XML mapper.

step 1: use the following statements to create a xml file:

libname testfile xmlv2 'c:\temp\testing.xml';

proc copy in=work out=testfile;

   select local central;

run;

step2: open the above xml(testing.xml) in XML mapper, manually create a map file (testing.map) via drag and drop method and modify label, change data type, formats etc in the property tabs.

Send both xml and map files to recipient. He or she can use the following statements to restore 'local' and 'central' datasets:

filename mapfile  'c:\temp\testing.map';

filename testfile 'c:\temp\testing.xml';

libname testfile xmlv2 xmlmap=mapfile ;

proc copy in=testfile out=work;

run;

If we have many datasets or many variables need to be transfered, we can use PROC CONTENTS to get meta data, and use DATA step to create a xml mapping file since the structure of map file is very simple. I will give a try when I have a chance. Thanks again!

Helen

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 851 views
  • 6 likes
  • 4 in conversation