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?
> 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.
@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.
@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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.