DATA Step, Macro, Functions and more

Converting character type date to SAS date

Reply
Occasional Contributor
Posts: 8

Converting character type date to SAS date

[ Edited ]

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;
Super User
Posts: 6,928

Re: Converting character type date to SAS date

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: Converting character type date to SAS date

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.
Super User
Posts: 17,777

Re: Converting character type date to SAS date

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....

Occasional Contributor
Posts: 8

Re: Converting character type date to SAS date

[ Edited ]

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.

Super User
Posts: 17,777

Re: Converting character type date to SAS date

[ Edited ]

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.

Super User
Posts: 6,928

Re: Converting character type date to SAS date

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,115

Re: Converting character type date to SAS date

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 */
Super User
Posts: 6,928

Re: Converting character type date to SAS date

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!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,928

Re: Converting character type date to SAS date

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 18

Re: Converting character type date to SAS date

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.

Occasional Contributor
Posts: 8

Re: Converting character type date to SAS date

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.
Ask a Question
Discussion stats
  • 11 replies
  • 456 views
  • 0 likes
  • 5 in conversation