topic Different date formats in Excel file output in New SAS User

Different date formats in Excel file output

mmea — Tue, 15 Dec 2020 09:20:52 GMT

Hi. I have a data set made with proc SQL with a large number of data.

I then exported the data in multiple csv files.

However my output shows different data format.

How can I fix this?

sampledate
01-dec-20
30-nov-20
23-apr-20
24-aug-20
26-sep-20
28-sep-20
26-aug-20
06-sep-20
01-sep-20
16-sep-20
27MAY2020
13OCT2020
18-aug-20
31OCT2020
19MAY2020

Re: Different date formats in Excel file output

PeterClemmensen — Tue, 15 Dec 2020 09:24:22 GMT

How did you do the export?

Re: Different date formats in Excel file output

Kurt_Bremser — Tue, 15 Dec 2020 09:34:18 GMT

IMO, this can only happen (in a simple export) if these "dates" are in fact inconsistently formatted strings in character variables, and not SAS date values.

Re: Different date formats in Excel file output

mmea — Tue, 15 Dec 2020 09:38:17 GMT

data dataset;
set dataset;
ID = _n_;
run;

data _null_;
set datasetnobs=n;
call symputx('antal_gange', ceil(n/1000000));
run;

data export;
set dataset;
if 1000000<=_n_<2*1000000;
run;

%macro dummy();

%do i=1 %to &antal_gange ;

data export;
set dataset;
if (&i-1)*1000000<_n_<=&i*1000000;
run;

proc export data=export
replace
outfile="F:\DATA_&i..csv"
dbms=dlm
;
delimiter=';';
quit;

%end;

%mend;

%dummy();

Re: Different date formats in Excel file output

mmea — Tue, 15 Dec 2020 09:39:52 GMT

in the dataset in SAS when I view the table, it does not show different formats of dates, all are the same. It is only when I export til excel.

Re: Different date formats in Excel file output

Kurt_Bremser — Tue, 15 Dec 2020 09:45:42 GMT

Please post an example of your dataset in a data step with datalines, make sure that this data step creates an excerpt identical to your dataset in terms of content and attributes. Also post the code you used to export your data to Excel.

We need this to be able to recreate your issue in our environments.

Post all the code by copy/pasting it into a window opened with the "little running man" button right next to the one indicated here:

The indicated button is the one to use for logs and text data.

Re: Different date formats in Excel file output

Kurt_Bremser — Tue, 15 Dec 2020 09:56:07 GMT

Inspect the contents of your CSV files with a text editor before opening them with Excel. Excel will show you what it thinks is in the data, and Excel is not good at doing that. But the actual contents of the files might give us (and you) a hint how to do the export in a consistent manner. In particular, which formats to set for the date variables so that Excel can consistently recognize them as dates. Since this is also dependent on your locale, please tell us how that is set on your Excel computer.

Re: Different date formats in Excel file output

mmea — Tue, 15 Dec 2020 09:58:32 GMT

/*Dataset created*/



proc sql;
   create table Data as
   select *
   from connection to XX(
      select sampleDate, XX,XX
      from [XX].[XX].[XX]  with(nolock)
	  where XX= 710
   );
quit;

then i do this for exporting

data dataset;
set dataset;
ID = _n_;
run;


data _null_;
set datasetnobs=n;
call symputx('antal_gange', ceil(n/1000000));
run;


data export;
set dataset;
if 1000000<=_n_<2*1000000;
run;

%macro dummy();

%do i=1 %to &antal_gange ;

data export;
set dataset;
if (&i-1)*1000000<_n_<=&i*1000000;
run;


proc export data=export
replace
outfile="F:\dataset_&i..csv" 
  dbms=dlm
;
delimiter=';';
quit;

%end;

%mend;

%dummy();

Re: Different date formats in Excel file output

mmea — Tue, 15 Dec 2020 10:01:33 GMT

i tried open the CSV file in textedit file, and the date shows the same format for all: e.g. "01JAN2020".

But if i open the csv in excel then it will change to another format as showed before.

Re: Different date formats in Excel file output

Kurt_Bremser — Tue, 15 Dec 2020 10:32:52 GMT

@mmea wrote:

i tried open the CSV file in textedit file, and the date shows the same format for all: e.g. "01JAN2020".

But if i open the csv in excel then it will change to another format as showed before.

Ok.
BIG HINT:

Do NOT use the DATE9. format for anything that has to go outside SAS. AFAIK, this format is only used (as a default) in SAS, and no other software (that I know of). Use a format that is very clearly understood to be a date, like YYMMDD10.

Or a format that correlates to the default format that Excel uses when you enter a date there (as already said, this is dependent on your locale).

Re: Different date formats in Excel file output

mmea — Tue, 15 Dec 2020 11:07:21 GMT

Thank you

is there a way to change the format directly in the proc sql statment?

proc sql;
   create table DATA as
      select p.sampledate
      from [IB_Outbreak].[SARS2].[PatientLineListe_V2] as p 
	  where XXXXX
   );
quit;

Re: Different date formats in Excel file output

Kurt_Bremser — Tue, 15 Dec 2020 12:12:01 GMT

Use a FORMAT= option in the SELECT:

proc sql;
   create table DATA as
      select p.sampledate format=yymmdd10.
      from [IB_Outbreak].[SARS2].[PatientLineListe_V2] as p 
	  where XXXXX
   );
quit;

Note that your code will throw an ERROR for the surplus closing bracket.