Help using Base SAS procedures

Create Datetime for updated SQL table

Reply
Occasional Contributor
Posts: 18

Create Datetime for updated SQL table

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 ;

Super User
Posts: 17,836

Re: Create Datetime for updated SQL table

What about the dhms formula?

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

Occasional Contributor
Posts: 18

Re: Create Datetime for updated SQL table

Tried that to:

d = '12SEP2013'd;

  put d date.;

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

  put dt datetime.;

Occasional Contributor
Posts: 18

Re: Create Datetime for updated SQL table

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)

Ask a Question
Discussion stats
  • 3 replies
  • 445 views
  • 0 likes
  • 2 in conversation