BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
capam
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Tom
Super User Tom
Super User

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
;
capam
Pyrite | Level 9

Thanks. This works well.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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