topic Re: formated date display in SAS Programming

formated date display

capam — Tue, 08 Aug 2017 12:47:10 GMT

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.

Re: formated date display

RW9 — Tue, 08 Aug 2017 13:02:29 GMT

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.

Re: formated date display

Tom — Tue, 08 Aug 2017 13:03:02 GMT

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
;

Re: formated date display

capam — Tue, 08 Aug 2017 13:25:31 GMT

Thanks. This works well.