how to read date informats in proc sql

[thanikondharish]

how to read date(ddmmyyd10.) in proc sql .

I have tried below step but I didn't get any value...

proc sql ;
create table ex1 (dob informat=ddmmyyd10. format=date9.) ;
insert into ex1 values(10-12-2020) ;
quit;

or

proc sql ;
create table ex1 (dob informat=ddmmyyd10. format=date9.) ;
insert into ex1 values('10-12-2020'd) ;
quit;

how to take informats?

[Reeza]

You must specify data as a SAS date literal in a date9 format. Or use a INPUT() to convert it using the correct informat.

[thanikondharish]

I got in data step block
How to do in proc SQL

[Reeza]

The same way.

[ChrisNZ]

> I got in data step block. How to do in proc SQL

There is no equivalent to the data step's input statement in SQL.

SQL only accepts the actual value to be inserted, and does not use informats to transform these values.

[Tom]

---

@thanikondharish wrote:

how to read date(ddmmyyd10.) in proc sql .

I have tried below step but I didn't get any value...

proc sql ;
create table ex1 (dob informat=ddmmyyd10. format=date9.) ;
insert into ex1 values(10-12-2020) ;
quit;

or

proc sql ;
create table ex1 (dob informat=ddmmyyd10. format=date9.) ;
insert into ex1 values('10-12-2020'd) ;
quit;

how to take informats?

---

Date literals,  "xxxx"d , only work when the text in the quotes can be interpreted by the DATE informat. So assuming 10-12 is December tenth you would use:

insert into ex1 values('10DEC2020'd) ;

Or you could use %SYSFUNC() to generate an actual number of days since 1960 and insert that.

insert into ex1 values(%sysfunc(inputn(10-12-2020,ddmmyy10.)));

You could even add some more code and generate a date literal, (but why?).

insert into ex1 values("%sysfunc(inputn(10-12-2020,ddmmyy10.),date9.)"d);

PS There is no informat named ddmmyyD.  There is a FORMAT with that name, but the informat DDMMYY can read values that use any of the normal delimiters, so there is no need for a specific one for a dash as delimiter.

[ballardw]

---

@Tom wrote:

---

PS There is no informat named ddmmyyD.  There is a FORMAT with that name, but the informat DDMMYY can read values that use any of the normal delimiters, so there is no need for a specific one for a dash as delimiter.

---

And I really appreciate SAS making DDMMYY, and other similar informats, that flexible.

I've had to deal with single source files with mixed content like:

data junk;
  input x :ddmmyy10.;
  format x date9.;
datalines;
12/02/2020
12-02-2020
12.02.2020
12022020
12*02*2020
12\02\2020
12|02|2020
;