BookmarkSubscribeRSS Feed
BhavukZutshi
Fluorite | Level 6

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;
11 REPLIES 11
BhavukZutshi
Fluorite | Level 6
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
Super User

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
Fluorite | Level 6

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

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

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
Jade | Level 19

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

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

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
Fluorite | Level 6

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
Fluorite | Level 6
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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 1589 views
  • 0 likes
  • 5 in conversation