Different date formats in Excel file output

[mmea]

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

[PeterClemmensen]

How did you do the export?

[mmea]

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();

[Kurt_Bremser]

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.

[mmea]

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.

[Kurt_Bremser]

---

@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).

[mmea]

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;

[Kurt_Bremser]

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.

[Kurt_Bremser]

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.

[mmea]

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.

[Kurt_Bremser]

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.

[mmea]

/*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();