topic Invalid Numeric Data in SAS Procedures

Invalid Numeric Data

BETO — Thu, 09 Feb 2012 08:41:08 GMT

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

Invalid Numeric Data

Tom — Thu, 09 Feb 2012 11:53:26 GMT

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

Invalid Numeric Data

BETO — Thu, 09 Feb 2012 15:26:20 GMT

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'

Invalid Numeric Data

art297 — Thu, 09 Feb 2012 15:31:42 GMT

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

Re: Invalid Numeric Data

DouglasMartin — Thu, 09 Feb 2012 15:36:39 GMT

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

Re: Invalid Numeric Data

Tom — Thu, 09 Feb 2012 15:42:11 GMT

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;

Invalid Numeric Data

BETO — Thu, 09 Feb 2012 17:21:37 GMT

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

Invalid Numeric Data

Tom — Thu, 09 Feb 2012 19:01:23 GMT

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.