Converting character type date to SAS date

[BhavukZutshi]

In my table grocery i'm having a variable named "couponexpiry" which has been imported into sas as a character, I am trying below code for converting it into date but in output only missing values I can see. Please help.  What is wrong in code ??

data test1; 
set class5.grocery_coupons; 
date_exp=input(couponexpiry,date9.); 
format date_exp date9.; 
run;

[Kurt_Bremser]

Please supply some example values. You may have used the wrong format. Also state which ERROR message you got.

[BhavukZutshi]

I have not got any error message. Only in output I am getting missing values. couponexpiry variable has dates in char format which i wanted in date format.

[Reeza]

Please provide sample values. What does your data look like? There's nothing 'wrong' about your code that we can see, but we can't see your data....

[BhavukZutshi]

My original data looked like below : couponexpiry 20-02-2014 21-02-2014 22-02-2014 23-02-2014 via using import command

proc import datafile ='/folders/myfolders/grocery_coupons.xls' out=class5.grocery_coupons dbms=xls replace; 
sheet= "Data" ;
getnames=Yes; 
datarow=2;
 run;

 I got the table in SAS but my couponexpiry column came as below couponexpiry 41690 41691 41692 41693 as char format so i used the aforementioned code in earlier post to convert this into date type format. I could not understand what error or mistake I'm making which is giving me missing values, may be a silly mistake. Help required.

[Reeza]

Your format isn't date9, its yymmdd10.

Change your input statement to reflect this and your code should work - barring any other errors.

Please use the Insert Code icon above to include code in your posts.

EDIT:

If your values look like numbers, are they numeric or character? It doesn't make sense that they've converted to numeric but are stored as chars.  You can try applying a format and see if it works, without an input statement.

[Kurt_Bremser]

Save data as .CSV from Excel, inspect it with a text editor, and then write a correct data step to read from the .CSV. .XLS is NOT suited for data transfer.

[FreelanceReinh]

Hello @BhavukZutshi,

Those "date values" 41690, 41691 etc. are Excel date values (with 1st Jan 1900 being day 1 and including the non-existent date 29 Feb 1900). You can simply subtract 21916 to obtain SAS date values, provided that you are not dealing with dates before 1st March 1900 (cf. this older thread).

Example:

data test1; 
couponexpiry='41690'; 
date_exp=input(couponexpiry, 8.)-21916; 
format date_exp date9.; 
run; /* formatted result: 20FEB2014 */

[Kurt_Bremser]

Although LibreOffice and OpenOffice work with data from Excel, the pure geniuses there managed to get rid of the 29feb1900 error by Applied Advanced Mathematics. They just moved day 1 to 31dec1899. And their software can therefore deal with dates before that, what a miracle!

[Kurt_Bremser]

You DO have messages in the log. Everytime data does not correspond to the format in an INPUT statement, you get NOTE: in your log

READ.THE.LOG.

From the looks, you need to use ddmmyy10. as the input format.

[ad123123]

Hi,

Not sure whether your query is solved or not....but I guess...following code will help.

data have;
coupunexpiry='20-02-2014';
output;
coupunexpiry='21-02-2014';
output;
coupunexpiry='22-02-2014';
output;
coupunexpiry='23-02-2014';
output;
run;

data want;
set have;
coupunexpiry=compress(coupunexpiry,'-');
num_dt=input(coupunexpiry,ddmmyy10.);
format num_dt date9.;
run;

Please let me know if it helps.

Regards,

Abd.

[BhavukZutshi]

Thanks my problem got solved. Mainly by first using file import as .csv and then putting in the right format which was ddmmyy10. Thanks to all the people for giving valuable suggestions and feedback.