topic Re: Different format date when export to csv file in SAS Programming

Different format date when export to csv file

ursula — Tue, 12 Jan 2016 18:24:45 GMT

Hi,

I did date format as mmddyy8. (01/01/16 -- with 2 digits year) in SAS, but when I exported to csv file, the format of the date changed to 4 digits year, and it also showed 4 digits year when I opened the csv file with Notepad.

is there a way to keep the date format as mmddyy8. (with 2 digits year) when export to csv file?

for example:

Date in SAS = 01/01/16, when export to csv file, I want the date format same as the format in SAS -- 01/01/16 in csv file.

Thanks in advance!

Re: Different format date when export to csv file

ballardw — Tue, 12 Jan 2016 18:27:06 GMT

Please show the code you used to export to CSV. There are a number of different ways to create a CSV file and the solution to the format issue may change depending on your choice.

Re: Different format date when export to csv file

Reeza — Tue, 12 Jan 2016 18:27:49 GMT

How are you exporting the data? When writing a CSV SAS honours formats applied usually, so you shouldn't have to change anything.

Re: Different format date when export to csv file

ursula — Tue, 12 Jan 2016 18:33:51 GMT

I was using this code to export the file.

PROC EXPORT DATA=test DBMS=CSV
FILE='test.csv';run;

Re: Different format date when export to csv file

Reeza — Tue, 12 Jan 2016 18:38:32 GMT

I can't replicate your issue with SAS 9.3 and proc export. Make sure the format is applied to your dataset, you can confirm this with a proc contents on your data set. Does the following generate a file with the two digit years for you?

data test;
do i=1 to 12;
test_date=mdy(i, 20, 2016);
output;
end;
format test_date mmddyy8.;
run;

proc export data=test outfile='c:\_localdata\delete.csv' dbms=csv replace;
run;

Re: Different format date when export to csv file

ursula — Tue, 12 Jan 2016 19:17:06 GMT

I run the code from you.

it showed 4 digits year if opened it in excel file.

But it showed 2 digets year when opened it in notepad, which is perfect!

For your review,

I used this format in my code.

PROC FORMAT;
VALUE test
-9="-9"
-1="-1"
0-HIGH=[MMDDYY8.];RUN;

data want;

set datahave;

format date test.;run;

PROC EXPORT DATA=want DBMS=CSV

FILE= “want.csv”;

RUN;

But the date changed to 4 digits year.

Re: Different format date when export to csv file

ursula — Tue, 12 Jan 2016 19:42:30 GMT

I run the code from you.

it showed 4 digits year if opened it in excel file.

But it showed 2 digets year when opened it in notepad, which is perfect!

For your review,

I used this format in my code.

PROC FORMAT;
VALUE test
-9="-9"
-1="-1"
0-HIGH=[MMDDYY8.];RUN;

data want;

set datahave;

format date test.;run;

PROC EXPORT DATA=want DBMS=CSV

FILE= “want.csv”;

RUN;

But the date changed to 4 digits year in csv file (open in ecxcel or notepad).

Re: Different format date when export to csv file

Reeza — Tue, 12 Jan 2016 20:30:13 GMT

Post your log from that code please, as well as the output from a proc contents on your actual dataset.

Proc contents data=want;

run;

Given that my code worked, its likely something incorrect in your process - one may be not having replace specified so that your new file isn't atually being created. Or the format isn't applied correctly to the want dataset.

Re: Different format date when export to csv file

ursula — Tue, 12 Jan 2016 20:36:11 GMT

you are right.

when I run Proc contents, it showed the date has an "informat" as date9 and format as mmddyy8 at the same time.

so I removed the informat, and it works!

Thank you so much for your effort to help, I appreciate your time.

Have a great day and Happy New Year!