BookmarkSubscribeRSS Feed
nimmis
Calcite | Level 5

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

9 REPLIES 9
ballardw
Super User

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
Calcite | Level 5

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

ballardw
Super User

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
Calcite | Level 5

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

ballardw
Super User

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
Calcite | Level 5

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
Super User

Again;

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

Also are you getting any messages in the log?

Reeza
Super User

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
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 11088 views
  • 0 likes
  • 4 in conversation