How to get rid of DATETIME format

[Tumtum]

I' have imported both csv and excel files into SAS, with exactly the same data. In excel, the birth_date has a datetime format of "10/25/1955 12:00:00 AM" symbolised by *DATE.

I ran this code to get the date in the format I need it.

proc datasets library=data;

modify population_2012;

format birth_date ddmmyy10.;

run;

quit;

However when I run a proc export  both the csv/xls files I've exported still end up with the datetime. I need to clean up the date in SAS so that I can load the good xls data into DB2.

Any suggestions?

[Reeza]

There are two issues:

Your applying a DATE format to a DATETIME variable so it won't change anything.

PROC EXPORT doesn't export formats to XLS if you check the log.

You'll need to convert the variable to a date variable in a datastep or export it another way.

[AncaTilea]

Instead of

proc datasets library=data;

modify population_2012;

format birth_date ddmmyy10.;

run;

quit;

You may want

data want;

     set  population_2012;

     new_dob = birthdate;

     format new_dob  ddmmyy10.;

run;

Then you may want to try to use ODS Tagset instead of export (be sure to change the bolded items) (because the proc export does not keep formats)

        ods listing close;
        ods tagsets.excelxp path = "&output_path." file="&data..xml"
        style=analysis
        options(absolute_column_width='10,10,8,8,10,10,15,8,15,8,15,10,8,8,8,8,10,1­0,15,8,15,8,15,10,8,8,8,8,20'
        sheet_label=' ');
        ods tagsets.excelxp options(sheet_name="sheetname");

        proc print noobs label data = &data.;
        var your_variables;
        run;
        ods tagsets.excelxp close;
        ods listing;

Good luck!

[Tumtum]

Hi,

This was really helpful...thank you so much!

Though I didnt quite understand the ODS Tagset bit ( it's my first time hearing of it)  - it worked out great.

TumTum

[niam]

you can also use datepart function

It extracts the date section of a variable from its datetime

[PGStats]

To convert a DATETIME variable to a DATE variable you must use the DATEPART function and assign a new format. For instance :

data want;

set population_2012;

birth_date = datepart(birth_date);

format birth_date ddmmyy10.;

run;

alternatively, you could simply give the variable a format that properly reflects its value and it should export without a problem:

proc datasets library=data;

modify population_2012;

format birth_date datetime20.;

run;

quit;

PG

[Tumtum]

@PGStats

The datepart function didn't give me the desired results - infact it defaults the new variable to "0" i.e. 01/01/1960.

[Tom]

Sounds like you do NOT have datetime values in your SAS data.  Dates are stored as the number of days since 01JAN1960.  Datetime is stored as the number of seconds.  So trying to use the DATEPART() function on a date value will usually give you zero.

It really looks to me like your original problem is with Excel and not with SAS. 

Why are you using Excel as an intermediary to load data from SAS into DB2?  Check if you have SAS/Access to DB2 licensed.

If you let Excel automatically read a text file (such as a CSV file) it can make a lot of mistakes. If you cannot write directly to DB2 or Excel format then I suggest manually importing the text file into Excel.  That will allow you to specify how it treats each variable.

[CeCe]

Thanks!

[RichardinOz]

Start with understanding the differences in date and time handling between SAS and MS Excel.  SAS measures dates in days; and times and datetimes in seconds.  Excel measures times in fractions of a day, so dates are just the integer part of a datetimevalue.  Hence in Excel to 'convert' a datetime value to a date value you apply a format which ignores the fractional time content of the datetime value.  This approach will not work in SAS because of the orders of magnitude difference between the underlying datetime value and the corresponding date value.

If you are using Proc Import to import import xls files then your site has licensed the SAS Access to PC File Formats product.  This product manages the conversion from Excel date or datetime values automatically (it also has to apply an offset because day zero for Excel is 0JAN1900 (ie 31DEC1899)). SAS imports numerical values as numbers unless it identifies a date, time or datetime format in Excel, in which case the conversion takes place and SAS applies an appropriate SAS date, time or datetime format to the output value.  The csv import process does the same, based on the formatted date/time/datetime values it encounters.  However, in older versions of SAS at least it will not recognise the format 10/25/1955 12:00:00 AM and will simply import the data as text.  This may be why the datepart function gave the result zero.

You can make SAS datetime values "look" like date values using the DTDATE format.  But the underlying value is not changed.

Your options thus are

• do the conversion in Excel by simply changing the column format to a date format
• write (or convert) a datastep to read the csv data using a date informat eg MMDDYY10 to read the date part only of the text datetime data
• import the data from an xls sheet, check that the import has resulted in a numeric field in SAS, and apply the datepart function in a subsequent data or sql step in sas (also changing the format to a date format), as in response 2.