Help using Base SAS procedures

Update date field in one table from another table

Reply
Occasional Contributor
Posts: 5

Update date field in one table from another table

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

Super User
Posts: 10,550

Re: Update date field in one table from another table

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?

Occasional Contributor
Posts: 5

Re: Update date field in one table from another table

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

Super User
Posts: 10,550

Re: Update date field in one table from another table

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.

Occasional Contributor
Posts: 5

Re: Update date field in one table from another table

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

Super User
Posts: 10,550

Re: Update date field in one table from another table

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.

Occasional Contributor
Posts: 5

Re: Update date field in one table from another table

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

Super User
Posts: 10,550

Re: Update date field in one table from another table

Again;

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

Also are you getting any messages in the log?

Super User
Posts: 17,957

Re: Update date field in one table from another table

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.

Ask a Question
Discussion stats
  • 8 replies
  • 3161 views
  • 0 likes
  • 3 in conversation