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 ;
What about the dhms formula?
dt=dhms( d, 0, 0, 0);
Tried that to:
d = '12SEP2013'd;
put d date.;
dt=dhms(d,0,0,0);
put dt datetime.;
Solved the problem: It was in my update statement:
last_update = (SELECT
dt
FROM
bird.test1 b
WHERE
b.contact_id = mkt_onboarding_contact_history.contact_id)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.