Conver Char to mmddyy10.

[Denali]

Hi,

I have a Excel speadshet with date of birth (DOB) variable. It was in the normal format in the Excel as below:

02/21/1968

10/04/1936

02/05/1959

08/25/1959

05/17/1953

12/25/1946

06/13/1945

04/23/1963

03/10/1953

04/14/1948

09/22/1967

07/22/1951

09/19/1939

01/20/1955

07/24/1963

09/21/1942

09/01/1946

07/13/1947

03/30/1946

07/10/1937

07/10/1937

08/28/1961

01/13/1942

04/25/1939

03/09/1947

10/25/1963

05/07/1939

11/24/1945

However, after I import the dataset into SAS, some dates became weird numbers and the format was "char 10"

as below:

19447
15055
07/24/1963
20657
12/25/1946
17825
18731
17677
17677
17447
29557
19018
06/13/1945
03/09/1947
17610
13747
30776
30776
07/13/1947
12/31/1961
02/05/1959
21692
01/20/1955
16282
22668

How do I conver "char 10" back to the normal mmddyy10. format? Thank you!

[ballardw]

Welcome to the wonderful world of people entering data into Excel sloppily.

The numbers you see like 19447 are very likely ones that were actually dates in Excel the ones that look like 07/24/1963 were entered or pasted into Excel as text. So you have mixed data in the column in Excel.

You can test this by highlighting the column in Excel, right click, go to format cells and set to number (not GENERAL, Number format). You will still see those that were treated by Excel as text appear to be dates.

The easiest solution, i.e. not requiring re-entering or typing over values in Excel or moderately obnoxious code in SAS, is to export the file to CSV format from Excel (File-Save AS set type to CSV) and import that file. Since the CSV will be created as simple text that looks like dates then the import should treat all of them as dates unless you have other garbage in the column like "Not Entered" and such.

[Tom]

Looks like some of the cells in the column in Excel had date values and other cells had strings.  Since SAS datasets are NOT spreadsheets SAS had to make the variable CHARACTER to allow the strings to be stored.  When it does that SAS stores date values as the internal number that Excel uses (number of days since 1900) as a digit string.

Let's make a SAS dataset that simulates what you say you got.

data have;
  input string $10.;
cards;
19447
15055
07/24/1963
20657
12/25/1946
17825
18731
17677
17677
17447
29557
19018
06/13/1945
;

Now let's use that data to show you how to convert the character string you got from Excel into a date value.  Let's attach a SAS format to the variables so the date value displays in a human readable form.  I prefer to use YMD order to avoid confusing people trying to read the printouts (What month and day does 10/12/1945 represent?  Now what month and day does 1945/12/10 represent?)  

data want ;
  set have;
  date = input(string,??mmddyy10.);
  if missing(date) then date='30DEC1899'd + input(string,32.);
  format date yymmdd10.;
run;

Results

Obs    string              date

  1    19447         1953-03-29
  2    15055         1941-03-20
  3    07/24/1963    1963-07-24
  4    20657         1957-02-06
  5    12/25/1946    1946-12-25
  6    17825         1948-10-19
  7    18731         1951-04-13
  8    17677         1948-05-24
  9    17677         1948-05-24
 10    17447         1947-10-07
 11    29557         1980-12-02
 12    19018         1952-01-25
 13    06/13/1945    1945-06-13

Also you should double check any value you get where the day of the month is less than 13 since is is possible that Excel also misunderstood which style the users meant when they typed in the dates.  So the tenth example value above might have originally been July Tenth instead of October Seventh.