Update date field in one table from another table

[nimmis]

I have a simple sql procedure where I am trying to update one field in a table from another. Both the target and source tables have this field set as MMDDYY10. The SQL syntax works for text data type But not for a date data type. Could someone please tell me what I have to do so that my data gets populated? Code runs with no errors. The target field in this case ACTUAL_FUNDING_DT shows just a period.

fproc sql;

update work.INVENTORY_COPY  as u

   set ACTUAL_FUNDING_DT= (select ACTUAL_FUNDING_DT from WORK.CONFIG_BACKFILL as n

where u.SIMPLE_ID=n.SIMPLE_ID);

[ballardw]

I'll bet that you have missing values in the output data for the values of Simple_id in U but not in N but the others updated correctly. Is that the case?

[nimmis]

All the values in U got a period '.' put in. Nothing got updated correctly

[ballardw]

Might be time to post a sample of data, just a few records from each data set and just enough variables (if you have more than 3 or 4) to demonstrate the behavior AND what you expect the output to be.

[nimmis]

Is there a special date format that needs to be put in SAS for date datatype?

[ballardw]

Dates are integers, basically the number of days since 1 Jan 1960, with special interpretation when used with date functions. An update like this would not be affected by differing formats or even none.

Do you get any warnings in the Log?

If ALL of the values are set to . then I would verify that the values of SIMPLE_ID are compatible. If they are text you may have issues with leading spaces or possibly encoding.

[nimmis]

The reason I think it is date format is because, I ran this query below. It is essentially the same query. Just updating INDEX_ID  which is a text data type instead of date. This one works fine. All the INDEX-ID got updated as expected-wherever there is a match of simple_id in both tables.

update work.INVENTORY_COPY  as u

   set INDEX_ID = (select INDEX_ID from WORK.CONFIG_BACKFILL as n

where u.SIMPLE_ID=n.SIMPLE_ID);

[ballardw]

Again;

Post some example data from your data sets that exhibit the error.

Also are you getting any messages in the log?

[Reeza]

Post a proc contents from both tables?

Also do a manual selection to ensure that there is data for what you're trying to backfill in and they aren't actual all missing.

[eduqlm]

A bit late, but as I arrived here, maybe it's usefull for another one.

You are missing one more final "where" clause, that ensures that if there is no correlation between tables, it leaves the actual value, and not an empty value.

update    work.INVENTORY_COPY as u
    set ACTUAL_FUNDING_DT= (
        select ACTUAL_FUNDING_DT
        from WORK.CONFIG_BACKFILL as n
        where u.SIMPLE_ID=n.SIMPLE_ID
    )
where u.SIMPLE_ID in (select SIMPLE_ID from WORK.CONFIG_BACKFILL);

More details here:

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p0z9p6hclwnhxin1mr...