Proc export TXT format assistance

[Q1983]

data test1;

input Group $ Date1 date9.;

datalines;

AARP 1JUN2013

BBPP 12AUG2016

;

run;

data test2;

set test1;

format date1 date9.;

run;

　

proc export = test2

OUTFILE= "&dorsshare.\GS\citiday..txt"

dbms=DLM

replace;

delimiter='|';

run;

When the end user receives the file and imports it, for some reason the date1 imports as a text.  The exact thing happened when I imported in MS Access.  The date defaults to text even though it was defined as a date in the datastep.  Do I need to change date1 to a numeric or date at proc export runtime???

[Tom]

A text file has no way to tell the user what format each variable contains. Everything is text and how to interpret the text is left to the consumer of the text file.

Ask them what format their system expects to see DATE values.

You could try using YYMMDD10. as the format instead of DATE9.  Then the text in the file will look like 2016-08-12 instead of 12AUG2016 and perhaps their software will recognize those as dates.  I would avoid MMDDYY or DDMMYY format to avoid having August 12th confused for December 8th.

Some systems do not have a concept of DATE value. Only DATETIME (or timestamp).  You might need to add a time part to your dates to get them to automatically recognize them as representing a datetime value.

[AMSAS]

Hi,

Yes, it will be imported as a text value, as the export file contains text values e.g. 01JUN2013 & 12AUG2016

You have several options to solve this, and it depends what your needs are

1) You could use PROC CPORT & CIMPORT to transfer the SAS dataset

2) You could drop the format, then the export text file will contain a numeric e.g. 19150 & 20678. Then apply a format to that variable after the other user reads in the text file

3) You could leave it as is, then use the INPUTN function to convert the character date to a numeric

Andy

[AMSAS]

My examples are for moving a SAS dataset between SAS environments, I didn't consider the solutions Tom mentions which move the dataset from SAS to some other application/database.
Just goes to show that it depends on what you are trying to achieve

[SuryaKiran]

You need to provide the format in a way how the consumer accepts. In your case if your exported file is later imported in Access then you need to format values in such a way that Access understands. Try format mmddyy10. instead and also while importing in Access you need to tell the format to it. 

See the screenshot for Access:

[Tom]

You can add S to the format to tell SAS to use slashes instead of hyphens as the delimiter.

YYMMDDS10.

I still wouldn't use either MMDDYY or DDMMYY. Either way your date strings will confuse half of your American/British friends.