Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

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

[ Edited ]

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 Screen Shot 2017-05-22 at 2.07.14 AM.png

 

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

 

Screen Shot 2017-05-22 at 2.20.47 AM.png

 

 

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

 

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

 

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.

Attachment

Accepted Solutions
Solution
‎05-22-2017 12:20 PM
Super User
Posts: 6,972

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,430

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

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;
Contributor
Posts: 22

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

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.
Solution
‎05-22-2017 12:20 PM
Super User
Posts: 6,972

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 22

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

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

Super User
Posts: 6,972

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 22

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

I have replaced the excel file with a sas7bdat file.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 430 views
  • 1 like
  • 3 in conversation