topic Re: how to convert char var to sas date? in SAS Programming

how to convert char var to sas date?

ZRick — Fri, 03 Feb 2012 01:27:17 GMT

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?

how to convert char var to sas date?

Haikuo — Fri, 03 Feb 2012 01:49:47 GMT

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

how to convert char var to sas date?

art297 — Fri, 03 Feb 2012 04:37:05 GMT

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

how to convert char var to sas date?

Patrick — Fri, 03 Feb 2012 07:51:29 GMT

Strip() is sufficient.

data _null_;
length havevar $ 80;
havevar=' 05/21/2010 ';
wantvar=input(strip(havevar),mmddyy11.);
put wantvar= date9.;
run;

how to convert char var to sas date?

art297 — Fri, 03 Feb 2012 13:29:48 GMT

Patrick, I added the substr function in the event that there are additional characters that strip wouldn't remove.

Re: how to convert char var to sas date?

Linlin — Fri, 03 Feb 2012 13:57:28 GMT

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;

Re: how to convert char var to sas date?

angeli — Mon, 28 Jan 2013 11:41:18 GMT

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

Re: how to convert char var to sas date?

data_null__ — Mon, 28 Jan 2013 11:55:15 GMT

You can use and INFORMAT to read what I will assume is MONTH and YEAR

data _null_; 
   monyy = 'jan_09'; 
   date = input(monyy,monyy10.);
   put date=date9.; 
   run;

Re: how to convert char var to sas date?

angeli — Mon, 28 Jan 2013 20:54:14 GMT

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?

Re: how to convert char var to sas date?

data_null__ — Mon, 28 Jan 2013 21:48:36 GMT

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.

Re: how to convert char var to sas date?

angeli — Thu, 07 Feb 2013 16:34:52 GMT

Thank you Data Null!

Re: how to convert char var to sas date?

NN2 — Fri, 29 Mar 2013 18:15:49 GMT

if I have a char of '2011-01-01', how can I convert it to SAS date value and format? thank you.

Re: how to convert char var to sas date?

Cynthia_sas — Fri, 26 Jul 2019 21:26:06 GMT

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;

Re: how to convert char var to sas date?

NN2 — Fri, 19 Apr 2013 17:09:00 GMT

Thank you, Cynthia.

Re: how to convert char var to sas date?

DebbiBJ — Fri, 04 Oct 2013 20:11:13 GMT

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

Re: how to convert char var to sas date?

Reeza — Fri, 04 Oct 2013 21:29:39 GMT

What do your dates look like:

01Jan2013 or 01/01/2013?

Post your full code as well.

Re: how to convert char var to sas date?

Cynthia_sas — Fri, 04 Oct 2013 21:30:55 GMT

Hi:

It is a bad idea of piggy-back onto an older post. Initially, when I saw the top post from 2012, I almost stopped reading.

Here's the thing, you didn't show all your code. My guess is that your DATES array is referencing the original CHARACTER vars. Once those vars are declared as CHARACTER, you can't redefine them as numeric. So even though the INPUT function will turn a character string into a number given the right informat, you have to essentially make a new NUMERIC variable (or group of variables) to hold the new NUMERIC variables. My guess is that you might have defined both of your ARRAYs as character by accident. But since you did not show your ARRAY statements, it's hard to be sure.

cynthia

data testdate;

length cd1 cd2 cd3 $10;

array dates $ cd1-cd3; /* Character array */

array newdates nd1-nd3; /* Numeric array */

infile datalines;

input cd1 $ cd2 $ cd3 $;

do k=1 to dim(dates);

newdates{k}=input(strip(dates{k}),mmddyy10.);

put _n_= k= dates{k}= newdates{k}=date9.;

end;

return;

datalines;

01/01/2011 01/15/1960 11/15/1950

06/18/1953 07/14/1956 11/29/1984

;

run;

ods listing;

proc contents data=testdate;

run;

Re: how to convert char var to sas date?

DebbiBJ — Fri, 04 Oct 2013 22:41:33 GMT

Sorry Cynthia, I'm new to this community, so my etiquette is probably off...

I will post a thorough response on Monday; brain is too fried now. I appreciate your willingess to help. Have a lovely weekend!

Re: how to convert char var to sas date?

DebbiBJ — Mon, 07 Oct 2013 15:05:39 GMT

Good morning!

I think the root of my problem is in the data, which I've PROC IMPORTed from an excel file. I don't understand why, but the Import reads the same file differently on different runs. That is, the subsequent PROC CONTENTS reads some of the date variables as character and some as numeric; the puzzling part is that it's not consistent which ones it calls which (when I run the program more than once). The data came from someone else; I tried specifically formatting all the date columns as date fields (i've tried both generic and specific date formats), and then as character fields, but that did not solve the problem. Do you have any thoughts?

Re: how to convert char var to sas date?

DebbiBJ — Mon, 07 Oct 2013 22:23:20 GMT

OK, I've been plugging away at this all day. My problem is that I am PROC IMPORTING an Excel file that has 15 or so date variables (columns). Most of it works fine, but if a variable is is missing in the first record, then any subsequent values for that variable get Imported as a text field rather than a numeric or date field. In the Data step, I've managed to convert them all to numeric fields. But... if the first value is missing then the values that *are* present are converting to weird dates - e.g., 14MAR2072 instead of 13MAR2012. Haven't been able to figure out what to do about that. Help?