topic Re: proc sql date in SAS Procedures

proc sql date

BrahmanandaRao — Fri, 06 Mar 2020 10:45:06 GMT

proc sql;
create table order (oder_ID num(1),ProductName varchar2(25),	OrderDate num format=yymmdd10.;
insert into order values(1,'Geitost','2008-11-11'd);
insert into order values(2,'Camembert Pierrot','2008-11-09'd);
insert into order values(3,'Mozzarella	di Goviovanni','2008-11-11'd);
insert into order values(4,'Mascarpone Fabioli','2008-10-29'D);
quit;

date format

Re: proc sql date

ed_sas_member — Fri, 06 Mar 2020 10:48:55 GMT

Hi @BrahmanandaRao

In the CREATE TABLE statement, there is a missing parenthesis and a mispelling.

In the INSERT INTO statement, you must use a 'date' format to specify date values (such as date9.: DDMONYYYY)

proc sql;

create table order (oder_ID num(1),ProductName varchar(25), OrderDate num format=yymmdd10.);

insert into order values(1,'Geitost','11NOV2008'd);

insert into order values(2,'Camembert Pierrot','09NOV2008'd);

insert into order values(3,'Mozzarella di Goviovanni','11NOV2008'd);

insert into order values(4,'Mascarpone Fabioli','29OCT2008'd);

quit;

Best,

Re: proc sql date

BrahmanandaRao — Fri, 06 Mar 2020 10:57:55 GMT

orderdate num format=date9.

not working

Re: proc sql date

ed_sas_member — Fri, 06 Mar 2020 11:01:42 GMT

That's not what I said -> you need to specify values as '11NOV2008'd and not '2008-11-11'd

The below query is the correction:

proc sql;

create table order (oder_ID num(1),ProductName varchar(25), OrderDate num format=yymmdd10.);

insert into order values(1,'Geitost','11NOV2008'd);

insert into order values(2,'Camembert Pierrot','09NOV2008'd);

insert into order values(3,'Mozzarella di Goviovanni','11NOV2008'd);

insert into order values(4,'Mascarpone Fabioli','29OCT2008'd);

quit;

Re: proc sql date

Kurt_Bremser — Fri, 06 Mar 2020 12:50:26 GMT

You should start to actually read our answers to your "questions". You have been shown how to supply date literals (in SQL, on top) at least once, like here: https://communities.sas.com/t5/SAS-Programming/proc-sql-column-date-datatype/td-p/594431

And why are you abusing SQL for a simple creation of a dataset, that's much easier done in a data step with datalines.