DATA Step, Macro, Functions and more

Inserting yesterday's date with proc sql insert into

Accepted Solution Solved
Reply
SAS Employee
Posts: 20
Accepted Solution

Inserting yesterday's date with proc sql insert into

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


Accepted Solutions
Solution
‎05-17-2016 02:48 AM
SAS Employee
Posts: 20

Re: Inserting yesterday's date with proc sql insert into

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: Inserting yesterday's date with proc sql insert into

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.

SAS Employee
Posts: 20

Re: Inserting yesterday's date with proc sql insert into

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:

 

NameType
OWNERCharacter
TABLE_NAMECharacter
DATA_TIMEDate
PERIODCharacter
AKSIGORTA_STATUSNumeric
SAS_STATUSNumeric
AKSIGORTA_PROCESS_START_TIMEDate
AKSIGORTA_PROCESS_END_TIMEDate
SAS_PROCESS_START_TIMEDate
SAS_PROCESS_END_TIMEDate
SAS_LOAD_TYPECharacter
AKSIGORTA_LOAD_TYPECharacter
AKSIGORTA_COUNTNumeric
SAS_COUNTNumeric
Super User
Super User
Posts: 7,997

Re: Inserting yesterday's date with proc sql insert into

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.

SAS Employee
Posts: 20

Re: Inserting yesterday's date with proc sql insert into

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

 

Solution
‎05-17-2016 02:48 AM
SAS Employee
Posts: 20

Re: Inserting yesterday's date with proc sql insert into

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 737 views
  • 0 likes
  • 2 in conversation