Invalid Numeric Data

[BETO]

I have a Data step that subtracts  one date from another  the differ than is match  to if it was done on correct date.

Data Test1;

Set Test1;

date1= date2-date3;

Put "differ in" Date1;

run;

The second data step is

Date1 - Scheduled date which if it matches than its a yes if it doesn't than its a No.

The Scheduled date is format with informant 10$

How can I convert to date ? 

Thanks

[Tom]

You will need to convert the character string into a date value.

You can do this with the INPUT function.  Which format to use will depend on the format of the characters that are in the scheduled_date variable.

Once you have figured that out then you can create a boolean variable using a statement like this:

match = (date1 = input(scheduled_date,date10.) );

[BETO]

  This is my data step.  The 2nd data step  is the issue  where I try to format date to mmddyy8. whats in the column is 02/06/2012 it doesn't read it as date.I tried your suggestionI might be inserting it wrong no results... Thanks

data Table1;

set table1;

Scheduled_Settle=reportdate1-days;

put "diffrence in" Scheduled_Settle;

PUT scheduled_Settle MMDDYY8.;

data table1;

set table1;

date=(SETTLEMENT);

format date mmddyy8.;

run;

data table1;

set table1;

FORMAT ON_SCHEDULE $5.;

length On_schedule $5.;

ON_SCHEDULE = 'FALSE';

if date=Scheduled_Settle THEN ON_SCHEDULE='TRUE';

If DEPPULLDAYS ='Other' Then  ON_SCHEDULE='FALSE'

[art297]

Can you provide at least a sample of records from table1?

[DouglasMartin]

Is SETTLEMENT character or numeric? If it is character, just setting a date format won't convert it to a date. As Tom said, you would need something like

date=input(settlement,mmddyy8.);

[Tom · Accepted solution]

If the value of SETTLEMENT is the text string '02/06/2012' and that means 06FEB2012 then you should use MMDDYY informat to convert to a date.

You do not need to keep going back through the data to make the transformations.  Until you get it working avoid overwritting the existing file.

Try something like this.

data Table1_new;

  set table1;

  length date 8 On_schedule $5;

  Scheduled_Settle=reportdate1-days;

  date=input(SETTLEMENT,mmddyy10.);

  format reportdate1 scheduled_settle date mmddyy8.;

  ON_SCHEDULE = 'FALSE';

  if date=Scheduled_Settle THEN ON_SCHEDULE='TRUE';

  if DEPPULLDAYS ='Other' Then  ON_SCHEDULE='FALSE';

    put (reportdate1 days scheduled_settle deppulldays date on_schedule) (=);

run;

[BETO]

Tom,

It worked thank you so much ... I stayed up all night cracking my head on this ... Could you explain how it worked?  In layman's terms ... Again Thank you

[Tom]

The only statement that is doing anything non-obvious is the INPUT function.  What it is doing is converting the string of characters stored in the text variable SETTLEMENT into the number that SAS uses to store the equivalent date.  The particular informat that I used is for dates in the format MM/DD/YYYY.   If your text strings were in a different format then you would need to use a different informat.

The FORMAT statement is also important as it will allow you to understand the meaning of the numbers stored in the date variables. SAS actually stores dates as the number of days since 1/1/1960.

So SAS will create the new dataset Table1_New be reading in observations from the existing dataset TABLE1.

For each observation it will do these steps:

- Calculate Scheduled_settle as the day that is DAYS before ReportDate1.

- create the numeric variable DATE by decoding the string in the variable SETTLEMENT.

The logic for creating ON_SCHEDULE is just as you already had.