topic Date Format issue in Enterprise Guide in SAS Programming

Date Format issue in Enterprise Guide

tobyfarms — Mon, 19 Jun 2017 17:46:59 GMT

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;

Re: Date Format issue in Enterprise Guide

Reeza — Mon, 19 Jun 2017 17:48:40 GMT

What does the log say?

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

Re: Date Format issue in Enterprise Guide

tobyfarms — Mon, 19 Jun 2017 17:57:05 GMT

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

Re: Date Format issue in Enterprise Guide

Reeza — Mon, 19 Jun 2017 18:02:54 GMT

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.

Re: Date Format issue in Enterprise Guide

tobyfarms — Mon, 19 Jun 2017 18:35:54 GMT

That fixed it. Issue resolved!

Thanks @Reeza

Re: Date Format issue in Enterprise Guide

ballardw — Mon, 19 Jun 2017 19:23:53 GMT

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.