Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Hi all,

I'm trying to export datetime variables into a Stata .DTA with PROC EXPORT, but they end up getting stripped of the time information. This does not occur when I do DMBS=CSV. I am running SAS 9.3 TS Level 1M2 x64_7PRO on Win7 6.1.7601 if this helps. I've written a snippet of code to illustrate the problem below. Any help with this issue is most appreciated!

Cheers,

Josh

/* Input dates */

data test_dates_gmt;

    input all_dates & $17. all_datetimes_gmt & $25.;

    datalines;

    24-May-2014        24-May-2014 01:00 -0500

    20-Jun-2014        20-Jun-2014 12:24 -0800

    06-Apr-2014        06-Apr-2014 18:52 -0500

    21-Nov-2014        21-Nov-2014 06:53 +0500

    08-Jan-2014        08-Jan-2014 05:12 +1200

    09-Mar-2014        09-Mar-2014 23:59 -1200

    01-Jun-2015        01-Jun-2015 12:26 +0600

    ;

run;

/* Create date times */

data test_dates_gmt;

    set test_dates_gmt;

    dates=input(all_dates, date11.);

    datetimes=input(all_datetimes_gmt, datetime18.);

    format dates date.;

    format datetimes datetime.;

run;

/* Write to .DTA */

proc export data=test_dates_gmt

    outfile="c:\stat\dta_date_time_2015-02-15.dta"

    dbms=stata replace;

run;

/* Read in .DTA */

proc import out=test_dates_gmt_dta

    datafile="c:\stat\dta_date_time_2015-02-15.dta"

    dbms=stata replace;

run;

/* Write to .CSV */

proc export data=test_dates_gmt

    outfile="c:\stat\dta_date_time_2015-02-15.csv"

    dbms=csv replace;

run;

/* Display formats */

proc contents data=test_dates_gmt_dta; quit;


Accepted Solutions
Solution
‎02-23-2015 02:59 PM
Occasional Contributor
Posts: 8

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

I spoke to SAS technical support, and this confirmed my suspicions. The datasets are being written in an older Stata format which does not support these features.

Unfortunately, it looks like another format will have to do. Thanks for you input, everyone.

View solution in original post


All Replies
Occasional Contributor
Posts: 8

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Hi all,

I believe this may be relevant: From this link, it appears that datetime formats were introduced in Stata 10:

"Stata 12’s saveold command saves the data in the format used by Stata 8 and 9, which can also be read by Stata 10. However, this dataset format does not support datetime formats that were introduced in Stata 10 (emphasis added)."

I believe the default is Stata 8, but is there a way to have PROC EXPORT use a later Stata format, such as 10 or 12? I found this which gives me some hope:

"SAS/ACCESS supports Stata 12 and earlier versions under Microsoft Windows."

Hope this helps!

Josh

Occasional Contributor
Posts: 8

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Hi all,

If PROC EXPORT is unable to write datetime formats to a .DTA file (which appear to support datetime data in Stata versions 10+), should I instead call R from PROC IML and have R write the DTA file? I know R is able to write version 10 .DTA files using the write.dta function in the foreign package. I was hoping to avoid this unless PROC EXPORT is totally unable to handle Stata version 10 files, but this introduces additional complications into our programming (version limitations on R in PROC IML, running SAS with specific options, etc.).

Any thoughts?

Josh

Esteemed Advisor
Esteemed Advisor
Posts: 7,188

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Fraid I haven't used stata, however it can apparently read XPT files: How do I use a SAS data file in Stata?

Could try that, otherwise CSV?

Occasional Contributor
Posts: 8

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Thanks for your reply, RW9-

While the SAS XPORT format is an open standard (and I believe is used routinely in clinical trials), it does have several limitations, one of which is in variable names:

"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 (emphasis added)."

The reasons I'd prefer to use DTA are:

  1. They're readable/writable by SAS (via proc import), Stata, and R (via foreign's read.dta/write.dta) without much hassle or external programs - This is important, as my coworkers and I use a combination of R, SAS, and Stata 13.
  2. They support missingness codes, which are invaluable for indicating the reasons for missingness (.A-.Z are usable by Stata, whereas ._ is only usable in SAS) - this provides another avenue for metadata.
  3. They support 32 letter variable names (working with sizeable datasets with lots of variables, usually with long names)

CSV is an option, but doesn't have the formatting options of DTA and SAS7BDAT. I'd prefer not to use SAS7BDAT as it's less convenient with other statistical software without introducing other software, such as Stat Transfer.

Also, I'm also finding that the missingness codes in numeric variables are being stripped as they're written to DTA - is there another option I need to use in order to have missingness codes be exported?

Best regards,

Josh

Esteemed Advisor
Esteemed Advisor
Posts: 7,188

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Yes, XPT is quite old now and limited.  The only other thing I can think of is XML/Json (probably XML as its more widely used).  With that you could have number of bits of information, format, type, lengths etc. Its relatively easy to create from SAS just with a datastep.  Don't know what the import potential is of the recipient software though.

Anyways, good luck.

Esteemed Advisor
Posts: 6,634

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Actually, a data step writing .CSV lets you use all the formats available in SAS. Where is the problem?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Thanks again for the input, RW9 and Kurt - any help is always appreciated. I'll look into CSVs as an alternative, but as to my original question:

Firstly, it appears that SAS is truncating information without reporting that information is being lost. I don't know whether this is user error on my part (e.g. not supplying the proper options to proc export, resulting in the loss of fidelity) or a genuine limitation of proc export. I have done some digging to figure out why, but haven't been successful - I am currently operating under the assumption that SAS is writing older Stata files, which do not support datetimes: this is based off writing out a .DTA file, and reading it in with read.dta in R and looking at the version number - according to R, DTAs written by SAS are Stata 7SE. As pointed out in an earlier post, SAS/ACCESS documentation says it supports versions 10-12: I do know from experience that SAS can read Stata 12 files, but I have been unsuccessful in finding out how to write Stata 10-12 files, which would preserve the formats. This may also explain why the missingness codes are being stripped as well. In any case, if there were any loss of fidelity, I would hope that SAS warn that some formats can not be preserved, as it usually does.

[EDIT: Since I'm using 9.3, I looked up SAS/ACCESS 9.3 to see if the problem may be limited to my version of SAS, but in the documentation it states:

FILES

Import of all Stata versions under Microsoft Windows and UNIX are supported. Export of Stata version 8 and later is supported.]

Secondly, I don't have just SAS to think about - the data I generate support a multidisciplinary team, including those who use Stata and R. This means that users need to be able to read in the data (and ideally, metadata like missingness codes) in their package of choice and have it be ready to use without much fanfare. So far, the only data formats that seem to work in this regard are Stata 10+. If I write these out as CSV, won't the missingness codes cause numerics/dates to be cast as strings?

Thanks again for the responses - while I do like the human readability and platform-free nature of CSV, my concerns with it are its limitations in dealing with formatted data without much extra hassle.


Best regards,

Josh

Grand Advisor
Posts: 17,289

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Have you contacted SAS Tech Support? They may have answers, reasons, or at least it makes them aware of the situation.

Esteemed Advisor
Esteemed Advisor
Posts: 7,188

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Yep, as mentioned I haven't used the Stata format so can't really say there.  The XML suggestion should be a good one though, its portable text based and structured, and non-proprietary.  So it should be readable from any system/program, and be user readable as is.  Cuts out that whole convert from this proprietary format to this proprietary format and then to...

CSV is simliar, less verbose which is its main plus.  But with the loss of verbose'ness you lose some of the descriptive. 

Occasional Contributor
Posts: 8

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Thanks Reeza and RW9 for your replies:

Reeza, I'm having someone from my institution contact SAS technical support - I just wanted to make sure I'm not missing something painfully obvious. I'll post a reply, in case anyone else stumbles across this issue.

RW9, thanks for the heads up on XML - I've used the R XML package before to good effect, so I know that R can handle it. Our data natively come in XML format, and R has been invaluable in troubleshooting XML issues. From a cursory search, it looks like Stata may as well. If .DTA is a no-go, and XML can preserve formatting, that might be the way to go.

Cheers,

Josh

Grand Advisor
Posts: 17,289

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

I cant see how XML would be better than CSV and it just adds a lot of overhead.

A CSV with metadata document is much preferable in my opinion.

Esteemed Advisor
Esteemed Advisor
Posts: 7,188

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

Yes, lots of different factors on what transport format to use.  XML encapsulates the metadata within one document for instance, but is thus quite verbose.  Have a look at:

When and Why is XML preferable to CSV? - Stack Overflow

Solution
‎02-23-2015 02:59 PM
Occasional Contributor
Posts: 8

Re: Datetimes exported as date: PROC EXPORT (DMBS=DTA)

I spoke to SAS technical support, and this confirmed my suspicions. The datasets are being written in an older Stata format which does not support these features.

Unfortunately, it looks like another format will have to do. Thanks for you input, everyone.

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 903 views
  • 6 likes
  • 4 in conversation