Converting character to ddmmmyyyy date

[rick255]

Hello,

I'm using SAS Enterprise version 7.15 HF8 (7.100.5.6214) (64-bit). I'm trying to change a date using data pulled from a Teradata server. The properties indicate the original field is Date9 and the output in SAS displays the dates as "ddmmmyyyy". I created a new table where I'm changing the date to "01JUN2019".

SAS identifies the new date as a character by default. When I try to change the field to Date9, the output is in numerical format and is displayed as "21701". I appreciate any help.

PROC SQL;
CREATE TABLE Have AS SELECT DISTINCT
'01JUN2019' AS START_DATE,
GROUP BY 1 ;QUIT;

data want;
set have
new = input(start_date, date9.);
drop start_date;
rename new=start_date;
run;

[Kurt_Bremser]

---

@rick255 wrote:

Hello,

I'm using SAS Enterprise version 7.15 HF8 (7.100.5.6214) (64-bit). I'm trying to change a date using data pulled from a Teradata server. The properties indicate the original field is Date9 and the output in SAS displays the dates as "ddmmmyyyy". I created a new table where I'm changing the date to "01JUN2019".

SAS identifies the new date as a character by default. When I try to change the field to Date9, the output is in numerical format and is displayed as "21701". I appreciate any help.

PROC SQL;
CREATE TABLE Have AS SELECT DISTINCT
'01JUN2019' AS START_DATE,
GROUP BY 1 ;QUIT;

data want;
set have
new = input(start_date, date9.);
drop start_date;
rename new=start_date;
run;

---

'01JUN2019'

is a string, while

'01JUN2019'd

is a valid date literal. To display a SAS date value (like 21701) in human-readable form, use a proper date format, e.g. date9.

[Tom · Accepted solution]

To make a date literal you need to add the letter D after the quoted string. To run a SELECT statement in PROC SQL you need to include a FROM clause. 

Note if you did successfully create a date value but just forgot to attach a date type format to it then the value is still a valid date value.  You just need to display it using the format.  The value 21,701 is the right value for the data '01JUN2019'd.  So just attach the format.

Note it is much easier to make simple datasets using SAS code instead of SQL code.

data have;
  START_DATE = '01JUN2019'd;
  format START_DATE date9.;
run;

[rick255]

Thank you, I added "d" after '01JUN2019' in my original table and it worked