02-16-2016 06:39 AM - last edited on 02-16-2016 06:57 AM by Reeza
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;
02-16-2016 06:48 AM
Please supply some example values. You may have used the wrong format. Also state which ERROR message you got.
02-16-2016 07:10 AM - last edited on 02-16-2016 07:16 AM by Reeza
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.
02-16-2016 07:14 AM - edited 02-16-2016 07:17 AM
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.
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.
02-16-2016 07:47 AM
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.
02-16-2016 08:47 AM
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).
data test1; couponexpiry='41690'; date_exp=input(couponexpiry, 8.)-21916; format date_exp date9.; run; /* formatted result: 20FEB2014 */
02-16-2016 08:55 AM
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!
02-16-2016 07:44 AM - edited 02-16-2016 07:44 AM
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
From the looks, you need to use ddmmyy10. as the input format.
02-17-2016 01:46 AM
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.
02-17-2016 05:57 AM