FORMATTING DIFFERENCES WITH proc sql vs datastep

[rajeshm]

/* dttimevar --datetime20. */

data xx;
set yy;
dttimevar dtdate9.;
run;
/*when exporting above xx as csv,getting date in required format(11aug20)*/
/*proc export data=xx; outfile="/home/fk023682/outputs/first.csv" dbms=csv replace; run;*/

proc sql;
create table xx as select 
dttimevar format=dtdate9.
from yy;
run;
/* after exporting dataset as xlsx,getting date in long numer(1911945600), what should i do?

1.Same formats used but why export is giving different results in xlsx and csv?

[Reeza]

XLSX isn't CSV. Which type did you export it to? 

Did you check the CSV with a text editor or Excel? Excel will interpret the values itself so you need to check it via a text editor and ensure that Excel isn't changing your CSV. FYI - it can change the structure of dates from dd/mm/yy to mm/dd/yy and that's often one that gets missed easily. It's annoying as heck. 

---

@rajeshm wrote:

/* dttimevar --datetime20. */

data xx;
set yy;
dttimevar dtdate9.;
run;
/*when exporting above xx as csv,getting date in required format(11aug20)*/
/*proc export data=xx; outfile="/home/fk023682/outputs/first.csv" dbms=csv replace; run;*/

proc sql;
create table xx as select 
dttimevar format=dtdate9.
from yy;
run;
/* after exporting dataset as xlsx,getting date in long numer(1911945600), what should i do?

1.Same formats used but why export is giving different results in xlsx and csv?

---

[Kurt_Bremser]

1. as I told you in your other thread, csv will work, while xlsx obviously does not
2. your data step is garbage; Maxim 2: Read the Log!

 72         
 73         data xx;
 74         set yy;
 75         dttimevar dtdate9.;
            _________
            180
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 76         run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.XX may be incomplete.  When this step was stopped there were 0 observations and 3 variables.
 WARNING: Datei WORK.XX wurde nicht ersetzt, da da dieser Schritt angehalten wurde.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       

So you did not set the format at all.

[Kurt_Bremser · Accepted solution]

PS the difference is not between SQL and DATA step, but between CSV and XLSX.

[rajeshm]

Thanks for the answer. 

i did not notice csv and xlsx are different formats with the code.

explanation from my end:

different programs using different ways and finally exporting. I opened these output  files and noticed differences and myself blindly assumed this is happening bcaz of code but finally concluded xlsx and csv are different and thats why i am getting different results. 

csv exports including formats ?

xlsx willl interpret the dataset values ( i dont know how)  and will display the result, is it?

[Tom]

Details matter here so please show the code used to generate the output files.

A CSV file is just text.  The only metadata it can contain is the header row which is normally used as the names to use for the variables.  In general SAS will use the attached formats when writing data to text, so if you have a DTDATE9 format attached to your datetime variables then the text written to the CSV file will look like ddMONyyyy.  Note that if you had a character variable instead of a datetime variable and the values also looked like ddMONyyyy then what is written into the CSV file will look exactly the same.  How the program that reads the CSV files determines what to do with that you will have to investigate more.

An XLSX file actual has structure, so it can have metadata about what types of values it contains.  But what code you use to write the XLSX file can make a big difference about what metadata SAS creates.  For example you can use STYLE option on PROC REPORT and PROC PRINT to control what formatting metadata is stored in the XLSX file.  So you could, if you want, store the actual datateime values and tell Excel to display it in ddMONyyyy style that matches how the DTDATE9 format in SAS would display that datetime value.