Invalid date/time/datetime constant classification

[moseland]

I am trying to create a filtered output table from a previous table in my library. I am getting the following errors in the log when I run the code. I'm trying to filter only the observations where APPT_DATE is after August 14, 2014. The original APPT_DATE has the DATETIME format: 5/25/2014 8:30

65 DATA chop.part1;
66 set chop.visit;
67 where DICT_ENC_TYPE_KEY = '83'
68 AND APPT_DATE >= "14082014:00:00"dt
ERROR: Invalid date/time/datetime constant "15082014:00:00"dt.
69 AND AGE BETWEEN 1 AND 19;
ERROR: Syntax error while parsing WHERE clause.
70 run;

I believe the error is how I'm writing the datetime in line 68 but am unsure how to correctly input it. 

[ChrisNZ · Accepted solution]

dt strings must be in format  '01jan2018:00:00:00'dt

[Kurt_Bremser]

See here for the documentation of SAS constants(literals):

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lrcon&docsetTarget=p0cq7f...

[ballardw]

---

@moseland wrote:

I am trying to create a filtered output table from a previous table in my library. I am getting the following errors in the log when I run the code. I'm trying to filter only the observations where APPT_DATE is after August 14, 2014. The original APPT_DATE has the DATETIME format: 5/25/2014 8:30

The value you show looks very much like you copied from an Excel spreadsheet or perhaps an Access data table.

You also should show us the results for Proc Contents for the variable APPT_DATE from the data set CHOP.VISIT.

Depending on how you brought the data into SAS the value may not even be a datetime value.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates and such.

[moseland]

I think you're right in that the Datetime format I originally included looks off. While the 5/25/2014 8:30 date was how it looked in an original .csv, it's possible I incorrectly imported the .csv into SAS in the first place. When I look at the PROC CONTENTS, this APPT_DT column is now: APPT_DT Char 16 $16. $16. 

Since I need to specify a range I'll read up on how to correctly import dates into SAS. Thank you for this insight! 

[Kurt_Bremser]

You can fix your problem at the source by converting the string in the step that reads the csv:

data want;
infile datalines dlm=',' dsd;
input id $ _appt_dt :$16.;
appt_dt = dhms(input(scan(_appt_dt,1,' '),mmddyy10.),0,0,input(scan(_appt_dt,2,' '),time8.));
format appt_dt e8601dt19.;
datalines;
A,5/25/2014 8:30
;
run;

[ChrisNZ]

Or something like (untested)

data WANT;
  infile datalines dlm=', ' dsd;
  input ID $ DATE :ddmmyy. TIME time.;
  DTM = DATE*3600*24 + TIME;
  format DTM e8601dt19.;
datalines;
A,5/25/2014 8:30
run;

Also, all lower-case code is harder to read. My personal preference is to have user-defined names in uppercase and language keywords in lower case.

[Kurt_Bremser]

I use the upper/lowercase distinction for something different: Variables pulled from the production database are uppercase, those created in the data warehouse during or after ETL are lowercase.