SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating a variable and populating it with current date in SAS DI Studio

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

Creating a variable and populating it with current date in SAS DI Studio

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.

  • column ------------ expression
  • stc_crs_sec ------ trim(stc_course_name)||'*'||trim(stc_section_no)
  • snapshot_date -- &sysdate

 

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?


Accepted Solutions
Solution
‎10-15-2015 02:52 PM
Respected Advisor
Posts: 4,173

Re: Creating a variable and populating it with current date in SAS DI Studio

&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.

View solution in original post


All Replies
Solution
‎10-15-2015 02:52 PM
Respected Advisor
Posts: 4,173

Re: Creating a variable and populating it with current date in SAS DI Studio

&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.

Frequent Contributor
Posts: 77

Re: Creating a variable and populating it with current date in SAS DI Studio

[ Edited ]

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:

 

extract2.png


extract.png
Frequent Contributor
Posts: 77

Re: Creating a variable and populating it with current date in SAS DI Studio

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!

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 463 views
  • 1 like
  • 2 in conversation