topic Update date field in one table from another table in SAS Procedures

Update date field in one table from another table

nimmis — Thu, 16 Oct 2014 21:01:05 GMT

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

Re: Update date field in one table from another table

ballardw — Thu, 16 Oct 2014 22:06:07 GMT

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?

Re: Update date field in one table from another table

nimmis — Thu, 16 Oct 2014 22:09:29 GMT

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

Re: Update date field in one table from another table

ballardw — Thu, 16 Oct 2014 22:28:12 GMT

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.

Re: Update date field in one table from another table

nimmis — Thu, 16 Oct 2014 22:38:16 GMT

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

Re: Update date field in one table from another table

ballardw — Thu, 16 Oct 2014 22:50:16 GMT

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.

Re: Update date field in one table from another table

nimmis — Thu, 16 Oct 2014 22:57:17 GMT

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

Re: Update date field in one table from another table

ballardw — Fri, 17 Oct 2014 15:12:50 GMT

Again;

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

Also are you getting any messages in the log?

Re: Update date field in one table from another table

Reeza — Fri, 17 Oct 2014 15:21:58 GMT

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.

Re: Update date field in one table from another table

eduqlm — Mon, 11 Sep 2017 16:10:19 GMT

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#p0z9p6hclwnhxin1mrewxhdz5f26.htm