topic Re: Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format in SAS Procedures

Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format

shihabur — Mon, 22 May 2017 16:27:11 GMT

I am trying to add some new rows to an existing data file using proc sql/insert into. As you can see from the data format that the dates are in MMDDYY format. When I am inputing 120111 (12 January 2011) in the values statement I am getting this "11/07/2288"

in the output which clearly wrong. I want to know what is the right way to specify the "date" in this situation ? Attached the code and the data file below.

My data set looks like this

I am trying to insert 4 new rows using following code

data work.sales_data2;
set work.example;
run;

proc sql;
insert into work.sales_data2 
VALUES(120111,120111,1244118000,11100,"Orange_Juice", 2,4,32.80)
VALUES(120211,120211,1244118001,11101,"Chocolate",3,2,18.20 )
VALUES(120311,120311,1244118002,11102,"Candy",1,5,3.00 )
VALUES(120411,120411,1244118003,11103,"Orange_Juice",1,5,35.00 );
quit;

The output looks like this . But the date is not showing the desired format- which is supposed to be 12/01/11

-----------------------------------------------------------

Updating the post with final solution. Find the code below. (Thanks to KurtBremser)

data work.sales_data2;
set work.example;
format Order_date mmddyy10.;
format Delivery_date mmddyy10.;
run;

proc sql;
insert into work.sales_data2 
VALUES("01dec2011"d,"01dec2011"d,1244118000,11100,"Orange_Juice", 2,4,32.80);
quit;

The correct steps to follow are:

1. check the format of date values (in the original data set) using describe table

2. use format statement within the data step to ensure the new value added will be in proper format.

3. finally, use date literal (with quotation and d at the end) in your values statement to ensure that the dates are read properly.

Re: Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format

RW9 — Mon, 22 May 2017 07:55:33 GMT

Well, this depends on what the informat is for the variable in question. If your data is expecting mmddyy format and you are supplying ddmmyy format then it will not work. Can you post test data from your existing dataset, as a datstep so I don't have to guess. You could also save yourself a lot of time by doing:

data want;
  set work.sales_data2;
  informat ...;
  input...;

datalines;
...
;
run;

Re: Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format

Kurt_Bremser — Mon, 22 May 2017 09:14:01 GMT

When you want to set a SAS date variable with a numeric value, you need to supply it as a date literal, or you have to convert to the correct SAS date value before.

The raw value 120111 is the SAS date 11/07/2288 (120111 days since 1960-01-01).

Example:

data test;
format x1 yymmddd10.;
x1 = today();
run;

proc sql;
insert into test
values("12jan2011"d)
;
quit;

Re: Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format

Kurt_Bremser — Mon, 22 May 2017 09:16:47 GMT

BTW DO NOT supply example data in an Excel file. An Excel file

- is considered malware by most corporate firewalls and therefore blocked (also see the recent flap with ransom trojans)

- can not convey atttributes of dataset and columns

and therefore useless.

Write a data step that creates your example data, see my code.

Re: Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format

shihabur — Mon, 22 May 2017 15:46:56 GMT

I have replaced the excel file with a sas7bdat file.

Re: Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format

shihabur — Mon, 22 May 2017 15:50:24 GMT

proc sql describe table shows that -

Order_Date num format=MMDDYY10. label='Date Order was placed by Customer',

Delivery_Date num format=DATE9. label='Date Order was Delivered',

posting the test data as data step in a bit.

Re: Inserting date value using "Insert into" in PROC SQL when the date has MMDDYYw. format

shihabur — Mon, 22 May 2017 16:20:16 GMT

This works. Thanks a lot. I understand what I was doing wrong.