I have several tables where the data needs to be extracted and then parts of it updated into a SQL table. Each source table is named with a date so I need to create that date and update the datetime field in the sql table.
What I can't seem to do is create a date in sas with a format that works when I update it in SQL. I have tried a dozen different formats and methods to create a date field. I keep getting the error:
ERROR: last_update, a numeric column, can only be updated with a numeric expression.
ERROR: The following columns were not found in the contributing tables: dt
Here is my update code and my latest attempt:
DATA bird.test1 ;
SET birdout.Onboarding_060_em_20130918 ;
d = '12SEP2013'd;
put d date.;
dt=input(put( d ,date7.) || ':00:00:00', datetime.);
put dt datetime.;
RuN ;
PROC SQL ;
UPDATE
camp070t.mkt_onboarding_contact_history
SET
complete_em2 = 1 ,
url60 = (SELECT
url60
FROM
bird.test1 b
WHERE
b.contact_id = mkt_onboarding_contact_history.contact_id) ,
last_update = dt
WHERE
contact_id = (SELECT
contact_id
FROM
bird.test1 b
WHERE
b.contact_id = mkt_onboarding_contact_history.contact_id
)
;
QUIT ;
