BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shihabur
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
shihabur
Obsidian | Level 7

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.
Kurt_Bremser
Super User

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;
shihabur
Obsidian | Level 7

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

Kurt_Bremser
Super User

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.

shihabur
Obsidian | Level 7

I have replaced the excel file with a sas7bdat file.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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