DATA Step, Macro, Functions and more

formated date display

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

formated date display

I'm attempting to get a standard date display and the following code generates asterisks for sw_install_date: 

 

proc sql;
	create table InstallDate as
		SELECT
			FLT.vehicle_header,
			FLT.vehicle_no,
			FLT.occur_date,
			FLT.FAULT_CODE,
			FLT.FAULT_DESCRIPTION,
			min(FLT.occur_date) as sw_install_date format date9.
		FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT
			WHERE 
				FLT.vehicle_header = 'NS' and
	FLT.occur_date between '25Jul2017'd and datetime()
	group by FLT.vehicle_no
	;
quit;

Something is wrong with the way I've used the 'format' statement.

 

Thanks for your help.sw_install_date.JPG


Accepted Solutions
Solution
‎08-08-2017 09:24 AM
Super User
Super User
Posts: 7,988

Re: formated date display

Your mixing up dates and date times, each of these is a very different number and your where clause is invalid as well:

FLT.occur_date between '25Jul2017'd and datetime()

If FLT.OCCUR_DATE is a date variable, then the numeric of that will be comparable to '25Jul2017'd, however it will not be comparable to datetime().  Likewise if OCCUR_DATE is a datetime value then it will not be comparable to '25jul2017'd.

Judging by the screenshot you OCCUR_DATE is a DATETIME variable, hence you need to either datepart or format correctly:

proc sql;
  create table INSTALLDATE as
  select  FLT.VEHICLE_HEADER,
    	  FLT.VEHICLE_NO,
    	  FLT.OCCUR_DATE,
    	  FLT.FAULT_CODE,
    	  FLT.FAULT_DESCRIPTION,
    	  datepart(min(FLT.OCCUR_DATE)) as SW_INSTALL_DATE format date9.
  from    RMDEOAP.GETS_DW_EOA_FAULTS FLT
where FLT.VEHICLE_HEADER='NS' and datepart(FLT.OCCUR_DATE) between '25Jul2017'd and date() group by FLT.VEHICLE_NO; quit

This uses only the datepart of the datetime in all cases.

View solution in original post


All Replies
Solution
‎08-08-2017 09:24 AM
Super User
Super User
Posts: 7,988

Re: formated date display

Your mixing up dates and date times, each of these is a very different number and your where clause is invalid as well:

FLT.occur_date between '25Jul2017'd and datetime()

If FLT.OCCUR_DATE is a date variable, then the numeric of that will be comparable to '25Jul2017'd, however it will not be comparable to datetime().  Likewise if OCCUR_DATE is a datetime value then it will not be comparable to '25jul2017'd.

Judging by the screenshot you OCCUR_DATE is a DATETIME variable, hence you need to either datepart or format correctly:

proc sql;
  create table INSTALLDATE as
  select  FLT.VEHICLE_HEADER,
    	  FLT.VEHICLE_NO,
    	  FLT.OCCUR_DATE,
    	  FLT.FAULT_CODE,
    	  FLT.FAULT_DESCRIPTION,
    	  datepart(min(FLT.OCCUR_DATE)) as SW_INSTALL_DATE format date9.
  from    RMDEOAP.GETS_DW_EOA_FAULTS FLT
where FLT.VEHICLE_HEADER='NS' and datepart(FLT.OCCUR_DATE) between '25Jul2017'd and date() group by FLT.VEHICLE_NO; quit

This uses only the datepart of the datetime in all cases.

Super User
Super User
Posts: 7,076

Re: formated date display

Most likely your variable does not contain date value, but nstead if contains datetime values.

 

You could use the DTDATE format to display just the date part of the datetime values.

Also you need to use a datetime literal instead of a date literal in the WHERE clause.

create table InstallDate as
  SELECT FLT.vehicle_header
       , FLT.vehicle_no
       , FLT.occur_date
       , FLT.FAULT_CODE
       , FLT.FAULT_DESCRIPTION
       , min(FLT.occur_date) as sw_install_date format dtdate9.
  FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT
  WHERE FLT.vehicle_header = 'NS'
    and FLT.occur_date between '25Jul2017:00:00'dt and datetime()
  group by FLT.vehicle_no
;
Contributor
Posts: 52

Re: formated date display

Thanks. This works well.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 97 views
  • 0 likes
  • 3 in conversation