DATA Step, Macro, Functions and more

Date Format issue in Enterprise Guide

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Date Format issue in Enterprise Guide

Hello, the date format in the code below create blanks for the columns DAY, MO, YR, and DATE.

The rest of the results are fine except for the new date breakouts.


DATA SALES (keep= CUSTCODE MO DAY YR DATE HOUR_1-HOUR_24 KWH_TOT);
SET MONTHLY_SALES ;
IF OPCOCODE = "DPL";


DAY = DAY(LOAD_DATE) ;
MO= MONTH(LOAD_DATE);
YR= YEAR(LOAD_DATE) ;
DATE = MDY(MO,DAY,YR) ;

FORMAT DATE DATE7.;

KWH_SUB = (SUM(OF HOUR_1-HOUR_24)* 1000);
KWH_TOT = sum(KWH_SUB,(HOUR_2_*1000));
IF HOUR_2_>0 THEN HOUR_2 =MEAN(HOUR_2,HOUR_2_);
run;


Accepted Solutions
Solution
‎06-19-2017 02:34 PM
Super User
Posts: 19,815

Re: Date Format issue in Enterprise Guide

Posted in reply to tobyfarms

Then you have a datetime variable, not a date variable. 

You need to convert it to a date to use the MONTH/DAY/YEAR functions.

 

Use the DATEPART() function to convert them to dates first.

View solution in original post


All Replies
Super User
Posts: 19,815

Re: Date Format issue in Enterprise Guide

Posted in reply to tobyfarms

What does the log say?

And what's the type/format of the date variable?

Contributor
Posts: 44

Re: Date Format issue in Enterprise Guide

@Reeza

Here is a portion of the log pertaining to the date.


NOTE: Invalid argument to function DAY(1737763200) at line 29 column 9.
NOTE: Invalid argument to function MONTH(1737763200) at line 30 column 7.
NOTE: Invalid argument to function YEAR(1737763200) at line 31 column 7.
OPCOCODE=DPL LOAD_DATE=25JAN2015:00:00:00

 

The properties indicate being in datetime20. format.

Thanks.

Solution
‎06-19-2017 02:34 PM
Super User
Posts: 19,815

Re: Date Format issue in Enterprise Guide

Posted in reply to tobyfarms

Then you have a datetime variable, not a date variable. 

You need to convert it to a date to use the MONTH/DAY/YEAR functions.

 

Use the DATEPART() function to convert them to dates first.

Contributor
Posts: 44

Re: Date Format issue in Enterprise Guide

That fixed it.  Issue resolved!

Thanks @Reeza

Super User
Posts: 11,343

Re: Date Format issue in Enterprise Guide

Posted in reply to tobyfarms

And instead of pulling things apart to use this:

DATE = MDY(MO,DAY,YR) ;

 

Date = datepart(Load_date);

 

Unless you have a very specific reason later to have  month, day and year variables.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 160 views
  • 1 like
  • 3 in conversation