topic Re: Convert Chracter variable to data in SAS Programming

Convert Chracter variable to data

Malathi13 — Fri, 02 Aug 2019 14:54:23 GMT

Hi,
I have a question with dates. I have a character value for date of birth which is 36223 and I want to convert it to any date format either date9 or yymmdd or mmddyy. My excel shows that date as 3/5/1999 but when I imported the excel to SAS it shows as character with 36223, I tried to convert to sas date using date9 or any other formats it gives me 3/4/2059 which is very weird.

This is my program that I wrote to convert it to sas date format

data have;
set new;
birthdate=input(dob, yymmdd10.);
format birthdate date9.;
run;

I'm getting many missing values for other DOBs but some date of births that I get are not correct. As I mentioned above the character value of 36223 comes as 3/4/2059 after applying the format. Any help is greatly appreciated.

thanks
M

Re: Convert Chracter variable to data

Reeza — Fri, 02 Aug 2019 14:56:43 GMT

SAS it shows as character with 36223

Can you show a proc contents of the data set before you run an INPUT() statement?

You may need to convert it to a number first and then apply a format. In the INPUT() statement, the format you provide is an informat, which should represent what the data currently looks like, which in this case is 8.

If you run something like this, what do you get?

numVar = input(dob, 8.);
format numVar date9.;

Re: Convert Chracter variable to data

data_null__ — Fri, 02 Aug 2019 16:11:26 GMT

Looks like 36223 is a EXCEL date value.

63   data _null_;
64      x = 36223+'30DEC1899'd;
65      y = '04MAR1999'd;
66      put (x y)(=);
67      put (x y)(=date9.);
68      run;

x=14307 y=14307
x=04MAR1999 y=04MAR1999

Updated based on comments from @Tom

Re: Convert Chracter variable to data

Tom — Fri, 02 Aug 2019 15:30:25 GMT

What date do you think 36223 represents? Do you think it is the value that Excel uses to represent 1999-03-04?

First convert '36223' into a number and then adjust the number to how SAS counts days.

birthdate=input(dob, 32.) + '30DEC1899'd ;
format birthdate date9.;

Re: Convert Chracter variable to data

Tom — Fri, 02 Aug 2019 15:37:01 GMT

@data_null__ wrote:

Looks like 36223 is a EXCEL data value.

45   data _null_;
46      x = 36223+'31DEC1899'd;
47      y = '05MAR1999'd;
48      /*3/5/1999*/
49      put (x y)(=);
50      put (x y)(=date9.);
51      run;

x=14308 y=14308
x=05MAR1999 y=05MAR1999

36,233 is March 4th in Excel. For any date after Feb 1900 (raw value 61) need to use '30DEC1899'd. If you actually have values of 59 and 60 you will need to come up with a plan for how to map them.

Re: Convert Chracter variable to data

ballardw — Fri, 02 Aug 2019 16:57:11 GMT

Or perhaps alternatively go back to the Excel file; make sure the entire column is formatted the same for the date columns: do a File>Save As to CSV and import the CSV file.

Re: Convert Chracter variable to data

Malathi13 — Fri, 02 Aug 2019 19:18:01 GMT

Hi,

When I ran

numVar = input(dob, 8.);
format numVar date9.;

I got the numvar as 05MAR2059

Re: Convert Chracter variable to data

Malathi13 — Fri, 02 Aug 2019 19:21:10 GMT

Hi,

I can use this data step because I have around 85,000 observations ( I mean date of Births). I can't do this for each observation or value.

data _null_;
64 x = 36223+'30DEC1899'd;
65      y = '04MAR1999'd;
66      put (x y)(=);
67      put (x y)(=date9.);
68      run;

36223 is just one date of birth, I have 85,000 date of births that are not 36223.

Re: Convert Chracter variable to data

Malathi13 — Fri, 02 Aug 2019 19:25:25 GMT

Hi Tom,

Thank you so much, that was so perfect!!!!!!!!!!

Thanks a lot!