I am extracting fields from an Oracle Table using the Transformation > SQL > Extract transformation.
Within the Extract transformation, on the mappings tab, I have created a two new variables/columns and given each an expression.
After some manipulations, I will load this data into another Oracle table.
However, the date portion is giving me an issue. I added it, so the expression for the column stc_crs_sec is working just fine. However, after adding the date, the job fails with the following errors:
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, , =, , =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
And when I look at the log, I get:
3768 (&SYSDATE) as SNAPSHOT_DATE length = 8
NOTE: Line generated by the macro variable "SYSDATE".
3768 14OCT15
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ,
EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
What I don't understand is - I'm not comparing it, I just want 14OCT15 to be placed into the column "Snapshot_Date" for each observation.
How do I accomplish this?
&sysdate contains a string and not a SAS date value. That's why your code throws an error.
As expression use either:
"&sysdate"d or date()
You then need also to apply a date9. format for your target column SNAPSHOT_DATE.
&sysdate contains a string and not a SAS date value. That's why your code throws an error.
As expression use either:
"&sysdate"d or date()
You then need also to apply a date9. format for your target column SNAPSHOT_DATE.
That seems to have added an additional error.
Here is the log:
NOTE: Mapping columns ...
5307 proc sql;
5308 create view work.W1PO1ZZ7 as
5309 select
5310 STC_PERSON_ID as STU_ID,
5311 STC_COURSE_NAME,
5312 STC_TERM,
5313 STC_SECTION_NO,
5314 (TRIM(STC_COURSE_NAME)||'*'||TRIM(STC_SECTION_NO)) as STC_CRS_SEC length = 8,
5315 &SYSDATE as SNAPSHOT_DATE length = 8
______
22
76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: Line generated by the macro variable "SYSDATE".
5315 14OCT15
_____
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
5316 from &SYSLAST
5317 where STC_TERM = '15/FA'
5318 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
5319 quit;
174 The SAS System 09:27 Wednesday, October 14, 2015
NOTE: The SAS System stopped processing this step because of errors.
And here is a picture of my Extract Transformation Mappings tab:
Thank you, I solved my problem. I don't know why, but I had to shut down DI Studio twice before the solution you offered would actually work. Now it works fine though. Thank you!
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.