I have a variable CPN_DT that is char, length 80 format $80, has following value:
05/21/2010
07/01/2010
03/01/2011
How do I convert it to sas date? I used following, but it doesn't work
cpn_dt = input(trim(cpn_dt),MMDDYY10.);
How can I fix this?
I really wonder why sas read this as char type with length of 80, because I got this variable via proc sql through odbc to oracle database.
why does SAS not recognize this as a mmddyy10. value?
Editor's Note: This topic is very popular! Cynthia's answer below is correct for the original question, helped by responses from @ballardw, @data_null__, and others. SAS Technical Support has also refreshed the SAS Sample: Convert variable values from character to numeric or from numeric to character. with many variations of char->num, char->date, num->char, and more. If you're just learning, check it out!
In addition, this 9-minute video demonstrates what to do:
Hi:
You would use a variation of data _null_'s example program. Basically, the INPUT function will convert a character string into a number. You need to use an INFORMAT (such as yymmdd10.) in order to instruct SAS how to do the conversion from character to numeric.
cynthia
data testdate;
length chardate $10;
infile datalines;
input chardate $;
date = input(chardate,yymmdd10.);
return;
datalines;
2011-01-01
1960-01-01
1984-11-29
2013-03-29
;
run;
ods listing;
proc print data=testdate;
title '1) Internally stored values';
run;
proc print data=testdate;
title '2) Using SAS format with dates';
format date worddate.;
run;
Well, it seems working for me, please see:
data have;
infile cards;
informat date $80.;
input date ;
cards;
05/21/2010
07/01/2010
03/01/2011
;
data want;
set have;
format _date mmddyy10.;
_date=input(trim(date),MMDDYY10.);
run;
proc print;run;
My suspicion is that you can't just convert char to numeric under the SAME variable name. You have to create another variable to the job, and if you want your old name back, you can always do it using date step options like: drop= rename=.
However, without seeing your code and log, I can be totally off on this one.
Regards,
Haikuo
I would guess that, given its length, it has more than that much extra blank space. Try:
cpn_dt = input(substr(strip(cpn_dt),1,10),MMDDYY10.);
Strip() is sufficient.
data _null_;
length havevar $ 80;
havevar=' 05/21/2010 ';
wantvar=input(strip(havevar),mmddyy11.);
put wantvar= date9.;
run;
Patrick, I added the substr function in the event that there are additional characters that strip wouldn't remove.
Hi Art,
I just noticed that if we use the same variable name, the variable converted by datastep is a char. variable, by SQL is a numeric variable. Thank you!
data have;
infile cards;
informat cpn_dt $80.;
input cpn_dt ;
cards;
05/21/2010
07/01/2010
03/01/2011
;
data want;
set have;
cpn_dt = input(substr(strip(cpn_dt),1,10),MMDDYY10.);
run;
proc contents data=want;run;
proc sql;
create table want1 as select input(substr(strip(cpn_dt),1,10),MMDDYY10.)
as cpn_dt format mmddyy10.
from have;
quit;
proc contents data=want1;run;
Hello All,
I need help converting a character into a numeric. The variable is CHAR 8 and in a format like Jan_09. Do I need to separate all the Months off and assign them a 1-12 value and separate the years and then put them together again.
Kindly assist..from a very new user!
Thanks
You can use and INFORMAT to read what I will assume is MONTH and YEAR
Thanks! SO then it makes a new variable called date which is in this format: 17898 How do I get this now to a date?
The new variable is a SAS Date it is the day offset from from the reference date. You can display the value with any SAS DATE format. See online docs. I used date9 in my example.
Thank you Data Null!
if I have a char of '2011-01-01', how can I convert it to SAS date value and format? thank you.
Editor's Note: This topic is very popular! Cynthia's answer below is correct for the original question, helped by responses from @ballardw, @data_null__, and others. SAS Technical Support has also refreshed the SAS Sample: Convert variable values from character to numeric or from numeric to character. with many variations of char->num, char->date, num->char, and more. If you're just learning, check it out!
In addition, this 9-minute video demonstrates what to do:
Hi:
You would use a variation of data _null_'s example program. Basically, the INPUT function will convert a character string into a number. You need to use an INFORMAT (such as yymmdd10.) in order to instruct SAS how to do the conversion from character to numeric.
cynthia
data testdate;
length chardate $10;
infile datalines;
input chardate $;
date = input(chardate,yymmdd10.);
return;
datalines;
2011-01-01
1960-01-01
1984-11-29
2013-03-29
;
run;
ods listing;
proc print data=testdate;
title '1) Internally stored values';
run;
proc print data=testdate;
title '2) Using SAS format with dates';
format date worddate.;
run;
Thank you, Cynthia.
Hi,
I found this thread trying to solve a similar problem, but am still not having any luck. PROC CONTENTS says that my original variables (hepb1b, hepb2b, polio1b, polio2b, etc. are character variables. They are all dates, and in an array called dates. I'm trying to convert them to numeric dates (via an array called dates2).
do k=1 to dim(dates);
dates2{k}=input(strip(dates{k}),mmddyy10.);
put _n_= k= dates{k}= dates2{k}=date9.;
end;
But, I get an error message about "format $date was not found..." indicating that, although a PROC PRINT shows SAS date values, they are still character. What am I doing wrong????
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.