Convert date character $CHAR6. to date DATE9.

[echoli]

Hi All,

I have a column, the format is $6. , I want to convert it into Date9. , I tried to used code below, but it doesn't work. 

data date_type;
set date_type;
if ParentCaregiverCompleted = "#NULL!" then delete;
PCC = put(input(ParentCaregiverCompleted||'01', yymmdd8.),date9.);
run;

The data in exced is below:

ParentCaregiverCompleted

05/19/15

11/05/14

but the data in my sas dataset is below:

ParentCaregiverCompleted

42143

41948

Any help to solve this?

Thanks,

C

[ballardw]

You seem to be missing some steps.

Please show some of the values for date_type BEFORE you manipulate them.

Also PUT will create a character variable. Generally not the best idea for dates.

And the values like 42143 look like the EXCEL numeric date values (number of days from 0/0/1900 yes that's not a valid date but just another Excel "eccentricity")

You may want to go back to the Excel file and set the entire column to a single date format. Sometimes EXCEL will allow mixing different things that look like dates but not all are. This might show that you have data entry differences. Sometimes this can be addressed by 1) saving the Excel data to CSV (file save as) and then use proc import on that file with a large value for the GUESSINGROWS option. The import engine for CSV can sometimes detect differing date formats and read using the anydtdte informat.

Or fix the data in Excel as you really may not be sure what you have as is.

[Reeza]

Definitely not the correct code. 

First try to apply a format and see what your date looks like 

format date_char DATE9.;

If this is incorrect you may need to add a correction factor. 

See the post here:

http://support.sas.com/kb/41/000.html

[Astounding]

Once we have the facts, this will become easy to solve.  But there is too much that is very questionable about your post.

Is the format $6 or $char6?

If it is either of those, why do your data values contain 8 characters?

If you took one of those data values, and add "01" to the end of it, you might get "05/19/1501".  Why does this string make any sense as a date?  How does it match up with a YYDDMM informat?

So take the SAS data set after importing, and run a PROC CONTENTS on it.  Run a PROC PRINT on a few observations.  Report back on what you see.  The coding isn't hard, it's knowing what is in the data.

[rogerjdeangelis]

Convert date character $CHAR6. to date DATE9. [ New ]

this message
https://goo.gl/XSNIq3
https://communities.sas.com/t5/SAS-Data-Management/Convert-date-character-CHAR6-to-date-DATE9/m-p/347777


HAVE excel workbook d:\xls\baddat.xlsx with sheet BADDAT (and named range BADDAT)
=================================================================================

You can use [sheet1] or '[sheet1$]'n for passthu, datastep respectively

BADDAT  sheet

 +------+---------------------------------------------+
 |      |                    A                        |
 +------+---------------------------------------------+
 |      |                                             |
 |    1 |  The data in exced is below:                |
 |    2 |  ParentCaregiverCompleted                   |
 |    3 |  05/19/15                                   |
 |    4 |  11/05/14                                   |
 |    5 |  but the data in my sas dataset is below:   |
 |    6 |  ParentCaregiverCompleted                   |
 |    7 |  42143                                      |
 |    8 |  41948                                      |
 +----------------------------------------------------+

 [BADDAT]

WANT (SAS dataset)
==================

Up to 40 obs WORK.WANT total obs=8

Obs    RECSEQ     BADDAT

 1        1              .
 2        2              .
 3        3      19MAY2015
 4        4      05NOV2014
 5        5              .
 6        6              .
 7        7              .
 8        8              .

WORKING CODE
==============

  MS SQL
      iif(InStr(baddat,'/')>0,baddat,'.') as baddat

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;

libname xel "d:/xls/baddat.xlsx";

data xel.baddat;
   length baddat $64;
   input;
   baddat=_infile_;
cards4;
The data in exced is below:
ParentCaregiverCompleted
05/19/15
11/05/14
but the data in my sas dataset is below:
ParentCaregiverCompleted
42143
41948
;;;;
run;quit;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

proc sql dquote=ansi;
  connect to excel (Path="d:\xls\baddat.xlsx");
    create
       table want as
    select
       monotonic() as recseq
      ,put(input(baddat,mmddyy8.),date9.) as baddat
    from
      connection to Excel
        (
         Select
              iif(InStr(baddat,'/')>0,baddat,'.') as baddat
         from
              baddat
        );
    disconnect from Excel;
Quit;


1721  proc sql dquote=ansi;
1722    connect to excel (Path="d:\xls\baddat.xlsx");

1723      create
1724         table want as
1725      select
1726         monotonic() as recseq
1727        ,put(input(baddat,mmddyy8.),date9.) as baddat
1728      from
1729        connection to Excel
1730          (
1731           Select
1732                iif(InStr(baddat,'/')>0,baddat,'.') as baddat
1733           from
1734                baddat
1735          );
NOTE: Table WORK."WANT" created, with 8 rows and 2 columns.

1736      disconnect from Excel;
1737  Quit;

[Tom]

Sounds like your method of importing from Excel did not convert the stored value from Excel Dates in either SAS dates or Character strings.  To convert your numbers to SAS dates you add the negative number that is the SAS date for 01JAN1900 and subtract 2 to account for difference in how the first day is done and one mistake that Excel has for a leap year somewhere along the way between 1900 and 1960.  Add the date format you want, preferrable one that uses four digits for the year.

data _null_;
  do have=42143, 41948 ;
    want = have + '01JAN1900'd -2 ;
    put (have 2*want) (=10. =10. =yymmdd10.);
  end;
run;

have=42143 want=20227 want=2015-05-19
have=41948 want=20032 want=2014-11-05