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

[GregG]

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?

[Patrick · Accepted solution]

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

[GregG]

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:

[GregG]

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!