Inserting yesterday's date with proc sql insert into

[dincoo]

Hi everyone,

I tried to insert yesterday's date into a table:

proc sql;
insert into x
(OWNER,
TABLE_NAME,
DATA_TIME,
PERIOD,
SAS_PROCESS_START_TIME,
SAS_LOAD_TYPE)
values
('SAS',
'ABT_TABLES',
%sysfunc(intnx(day,%sysfunc(date()), -1),date9.),     /* YESTERDAY*/
'DAILY',
%sysfunc(datetime()),
'TRUNCATE/INSERT'
);
quit;

But there is an error:

24 proc sql;
25 insert into x
26 (OWNER,
27 TABLE_NAME,
28 DATA_TIME,
29 PERIOD,
30 SAS_PROCESS_START_TIME,
31 SAS_LOAD_TYPE)
32 values
33 ('SAS',
34 'ABT_TABLES',
35 %sysfunc(intnx(day,%sysfunc(date()), -1),date9.),
NOTE: Line generated by the macro function "SYSFUNC".
35 11MAY2016
_______
22
202
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, ), +, ',', -, MISSING, NULL, USER.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Do you have any suggestions?

Many thnaks,

Onur

[RW9]

Hi,

After tidying up the code a bit, I see that the %sysfunc() returns a datetime value, but this is not valid to insert into a numeric.  I assume that data_time is a date field, and sas_process_start_time is a datetime field.  You will note that I quote the text (macro is always text!) from the sysfunc, and then put dt after it to mean the result is a datateime literal. 

proc sql;
  insert into X (OWNER,TABLE_NAME,DATA_TIME,PERIOD,SAS_PROCESS_START_TIME,SAS_LOAD_TYPE)
  values ('SAS','ABT_TABLES',today()-1,'DAILY',"%sysfunc(datetime())"dt,'TRUNCATE/INSERT');
quit;

Try that, if you still get problems, also post the code that creates the initial table.  Is there any reason why you would need to do this as an insert, you would find a simple datastep is easier especially for many rows or more complicated processing.

[dincoo]

Hello,

Thanks for your answer, there is a scheduled code which runs every night. My purpose is: if there is an error when running the code I am going to insert that the scheduled code gets an error

22 GOPTIONS ACCESSIBLE;
23 proc sql;
24 insert into X (OWNER,TABLE_NAME,DATA_TIME,PERIOD,SAS_PROCESS_START_TIME,SAS_LOAD_TYPE)
25 values ('SAS','ABT_TABLES',today()-1,'DAILY',"%sysfunc(datetime())"dt,'TRUNCATE/INSERT');
_____
22
202
ERROR: Invalid date/time/datetime constant "1778690767.80049"dt.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, ), +, ',', -, MISSING, NULL, USER.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

X Table variables type:

Name	Type
OWNER	Character
TABLE_NAME	Character
DATA_TIME	Date
PERIOD	Character
AKSIGORTA_STATUS	Numeric
SAS_STATUS	Numeric
AKSIGORTA_PROCESS_START_TIME	Date
AKSIGORTA_PROCESS_END_TIME	Date
SAS_PROCESS_START_TIME	Date
SAS_PROCESS_END_TIME	Date
SAS_LOAD_TYPE	Character
AKSIGORTA_LOAD_TYPE	Character
AKSIGORTA_COUNT	Numeric
SAS_COUNT	Numeric

[RW9]

Sorry, your losing me here, is this something whih is happening on the database?  If so what does SAS have to do with this, run your updates in the database?  This is what is really confusing me is this jumping back and forth between technologies here, database and SAS.  If its a SAS dataset then:

values ('SAS','ABT_TABLES',today()-1,'DAILY',today(),'TRUNCATE/INSERT');

Should work.

[dincoo]

Thanks for the details but I just only want to use proc sql insert into to write yesterday's date.

[dincoo · Accepted solution]

It works:

proc sql;
insert into ora.fraud_handshaking_log
set OWNER='SAS',
TABLE_NAME='ABT_FINAL',
DATA_TIME = intnx('dtday', datetime("&SYSDATE"d), -1),
PERIOD='DAILY',
SAS_PROCESS_START_TIME=%sysfunc(datetime()),
SAS_LOAD_TYPE='TRUNCATE/INSERT',
SAS_STATUS = 0;
quit;