topic Re: formatting date variable returns JAN1960 in SAS Programming

formatting date variable returns JAN1960

Haydn — Wed, 09 Sep 2020 23:13:37 GMT

Hello,

I have a variable called "Date". The dataset is imported from a SharePoint list and is imported as DATE9.

I have coded

Report_Month=datepart(Date);
format Report_Month monyy7.;

An example of the "Date" variable is 08SEP2020. When looking at the properties of this variable it's Type(Numeric),Group (Date) Format(DATE9.) and Informat (DATE9.)

This returns JAN1960 for every row.

Can someone please assist?

Re: formatting date variable returns JAN1960

ChrisNZ — Wed, 09 Sep 2020 23:24:01 GMT

Your DATE variable is a datetime, not a date.

Format it with format DATETIME20.

Re: formatting date variable returns JAN1960

Haydn — Wed, 09 Sep 2020 23:28:44 GMT

Thank you for your quick response.
I coded
Report_Month=datepart(Date);
format Report_Month DATETIME20.;

and it returns 01JAN1960:00:00:00

Re: formatting date variable returns JAN1960

ChrisNZ — Wed, 09 Sep 2020 23:34:18 GMT

1. Why do you format variable REPORT_MONTH when we are discussing variable DATE?

2. What' s the values when formatted with format comma32.16 ?

Re: formatting date variable returns JAN1960

Haydn — Wed, 09 Sep 2020 23:40:53 GMT

1. I'm creating a new variable called Report_Month based off the Date variable
2. I think I know where I was going wrong. I was using datepart
The below worked. Thanks for your input, it steered me in the right direction. I was over complicating it.
Report_Month=Date;
format Report_Month monyy7.;

Re: formatting date variable returns JAN1960

jimbarbour — Wed, 09 Sep 2020 23:58:00 GMT

@Haydn,

Can you tell me the exact data you are exporting from SharePoint? Is it text that represents a date or is it an actual DateTime field? If you are exporting a DateTime field, if I recall correctly, you have to compensate for the difference between the way .Net represents a DateTime and SAS. SAS DateTime values are a count of seconds starting on 01 Jan 1960. By contrast, .Net represents a DateTime value as the number of seconds 01 Jan 0001. There are 715,509 days between 01 Jan 0001 and 01 Jan 1960 (I looked it up), and there are 84,600 seconds per day. You would need to adjust a SharePoint DateTime value by subrtracting (715,509 * 84600) from it if I'm remembering my math correctly.

If you're reading character representations of a date, for example 09/09/2020, then you need to make sure you're using a Date format if it's a date (example 09/09/2020) or a DateTime format if the value is represents a DateTime value (example 2020/09/09:16:57:00). Be sure to used Date formats for dates and DateTime formats for DateTimes.

Jim

In addition, SAS doesn't consider 4000 or 8000 to be leap years. If you're dealing with long term dates, you might have to compensate for that difference in what is considered a leap year.