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);
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?
All the values in U got a period '.' put in. Nothing got updated correctly
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.
Is there a special date format that needs to be put in SAS for date datatype?
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.
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);
Again;
Post some example data from your data sets that exhibit the error.
Also are you getting any messages in the log?
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.
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:
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.