topic Re: Converting character to ddmmmyyyy date in SAS Programming

Converting character to ddmmmyyyy date

rick255 — Sat, 12 Oct 2019 19:13:57 GMT

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;

Re: Converting character to ddmmmyyyy date

Kurt_Bremser — Sat, 12 Oct 2019 19:27:55 GMT

@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.

Re: Converting character to ddmmmyyyy date

Tom — Sat, 12 Oct 2019 20:57:08 GMT

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;

Re: Converting character to ddmmmyyyy date

rick255 — Sun, 13 Oct 2019 16:48:07 GMT

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