Create Datetime for updated SQL table

paulsparrow — Fri, 27 Sep 2013 15:50:08 GMT

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 ;

Re: Create Datetime for updated SQL table

Reeza — Fri, 27 Sep 2013 15:52:12 GMT

What about the dhms formula?

dt=dhms( d, 0, 0, 0);

Re: Create Datetime for updated SQL table

paulsparrow — Fri, 27 Sep 2013 15:57:11 GMT

Tried that to:

d = '12SEP2013'd;

put d date.;

dt=dhms(d,0,0,0);

put dt datetime.;

Re: Create Datetime for updated SQL table

paulsparrow — Fri, 27 Sep 2013 16:00:20 GMT

Solved the problem: It was in my update statement:

last_update = (SELECT

FROM

bird.test1 b

WHERE

b.contact_id = mkt_onboarding_contact_history.contact_id)

topic Re: Create Datetime for updated SQL table in SAS Procedures

Create Datetime for updated SQL table

Re: Create Datetime for updated SQL table

Re: Create Datetime for updated SQL table

Re: Create Datetime for updated SQL table