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.
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.
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.
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
;
Thanks. This works well.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.