proc sql column date datatype

BrahmanandaRao — Mon, 07 Oct 2019 08:17:34 GMT

proc sql;
create table orders(ord_num char(6) , 
	ord_amount char(12) , 
	advance_amount char(12) , 
	ord_date date  , 
	cust_code char(6)  , 
	agent_code char(6) , 
	ord_description char(60));
insert into orders values('200100', '1000.00', '600.00', '08/01/2008', 'c00013', 'a003', 'sod');
insert into orders values('200110', '3000.00', '500.00', '04/15/2008', 'c00019', 'a010', 'sod');
insert into orders values('200107', '4500.00', '900.00', '08/30/2008', 'c00007', 'a010', 'sod');
insert into orders values('200112', '2000.00', '400.00', '05/30/2008', 'c00016', 'a007', 'sod'); 
insert into orders values('200113', '4000.00', '600.00', '06/10/2008', 'c00022', 'a002', 'sod');
insert into orders values('200102', '2000.00', '300.00', '05/25/2008', 'c00012', 'a012', 'sod');
insert into orders values('200114', '3500.00', '2000.00', '08/15/2008', 'c00002', 'a008', 'sod');
insert into orders values('200122', '2500.00', '400.00', '09/16/2008', 'c00003', 'a004', 'sod');
insert into orders values('200118', '500.00', '100.00', '07/20/2008', 'c00023', 'a006', 'sod');
insert into orders values('200119', '4000.00', '700.00', '09/16/2008', 'c00007', 'a010', 'sod');
insert into orders values('200121', '1500.00', '600.00', '09/23/2008', 'c00008', 'a004', 'sod');
insert into orders values('200130', '2500.00', '400.00', '07/30/2008', 'c00025', 'a011', 'sod');
insert into orders values('200134', '4200.00', '1800.00', '09/25/2008', 'c00004', 'a005', 'sod');
insert into orders values('200108', '4000.00', '600.00', '02/15/2008', 'c00008', 'a004', 'sod');
insert into orders values('200103', '1500.00', '700.00', '05/15/2008', 'c00021', 'a005', 'sod');
insert into orders values('200105', '2500.00', '500.00', '07/18/2008', 'c00025', 'a011', 'sod');
insert into orders values('200109', '3500.00', '800.00', '07/30/2008', 'c00011', 'a010', 'sod');
insert into orders values('200101', '3000.00', '1000.00', '07/15/2008', 'c00001', 'a008', 'sod');
insert into orders values('200111', '1000.00', '300.00', '07/10/2008', 'c00020', 'a008', 'sod');
insert into orders values('200104', '1500.00', '500.00', '03/13/2008', 'c00006', 'a004', 'sod');
insert into orders values('200106', '2500.00', '700.00', '04/20/2008', 'c00005', 'a002', 'sod');
insert into orders values('200125', '2000.00', '600.00', '10/10/2008', 'c00018', 'a005', 'sod');
insert into orders values('200117', '800.00', '200.00', '10/20/2008', 'c00014', 'a001', 'sod');
insert into orders values('200123', '500.00', '100.00', '09/16/2008', 'c00022', 'a002', 'sod');
insert into orders values('200120', '500.00', '100.00', '07/20/2008', 'c00009', 'a002', 'sod');
insert into orders values('200116', '500.00', '100.00', '07/13/2008', 'c00010', 'a009', 'sod');
insert into orders values('200124', '500.00', '100.00', '06/20/2008', 'c00017', 'a007', 'sod'); 
insert into orders values('200126', '500.00', '100.00', '06/24/2008', 'c00022', 'a002', 'sod');
insert into orders values('200129', '2500.00', '500.00', '07/20/2008', 'c00024', 'a006', 'sod');
insert into orders values('200127', '2500.00', '400.00', '07/20/2008', 'c00015', 'a003', 'sod');
insert into orders values('200128', '3500.00', '1500.00', '07/20/2008', 'c00009', 'a002', 'sod');
insert into orders values('200135', '2000.00', '800.00', '09/16/2008', 'c00007', 'a010', 'sod');
insert into orders values('200131', '900.00', '150.00', '08/26/2008', 'c00012', 'a012', 'sod');
insert into orders values('200133', '1200.00', '400.00', '06/29/2008', 'c00009', 'a002', 'sod');
quit;

Hi experts Good Morning in the above table i am getting an error regarding ord_date column date datatype in proc sql varchar2 not working or not please advise me Regards, Anand

Re: proc sql column date datatype

FreelanceReinh — Mon, 07 Oct 2019 09:54:14 GMT

Hi @BrahmanandaRao,

Basically, "date" variables in PROC SQL are numeric variables (they are just equipped with the DATE. format and the DATE. informat by default). So, the character values like '08/01/2008' in your INSERT statements are invalid. Provide date literals such as '01AUG2008'd (these are numeric) instead. If you don't want to rewrite all those values, you can read them as character values in the first step and convert them into numeric values in a subsequent step. Or convert them "on the fly" by using %SYSFUNC(INPUTN(...)), see example below. Note that you don't need to repeat the INSERT INTO statement because it accepts a series of VALUES clauses (not separated by semicolons).

insert into orders
  values('200100', '1000.00', '600.00', %sysfunc(inputn(08/01/2008,mmddyy10.)), 'c00013', 'a003', 'sod')
  values('200110', '3000.00', '500.00', '15APR2008'd,                           'c00019', 'a010', 'sod');

I'm not sure why you create ord_amount and advance_amount as character variables. Aren't 1000.00, 600.00, etc. numeric values to be used for calculations?

topic Re: proc sql column date datatype in SAS Programming

proc sql column date datatype

Re: proc sql column date datatype