topic Re: how to read date informats in proc sql in SAS Programming

how to read date informats in proc sql

thanikondharish — Fri, 13 Mar 2020 16:05:22 GMT

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;

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

how to take informats?

Re: how to read date informats in proc sql

Reeza — Fri, 13 Mar 2020 16:19:29 GMT

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

Re: how to read date informats in proc sql

thanikondharish — Fri, 13 Mar 2020 16:24:32 GMT

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

Re: how to read date informats in proc sql

Reeza — Fri, 13 Mar 2020 16:38:58 GMT

The same way.

Re: how to read date informats in proc sql

Tom — Fri, 13 Mar 2020 17:04:28 GMT

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

Re: how to read date informats in proc sql

ballardw — Fri, 13 Mar 2020 21:52:08 GMT

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

Re: how to read date informats in proc sql

ChrisNZ — Sat, 14 Mar 2020 10:48:38 GMT

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