SAS DI Studio - Execute SQL Mappings

[regulargal]

(Background: I'm new to SAS and my organization uses SAS Data Integration Studio 4.9. I predominantly use SQL and have been provided the SQL code to create tables in SAS for later use in SAS VA.)

Using the Execute SQL Transformation, how do I map a column to a field with an expression in the PROC SQL Select. I have tried putting the expression into both the SQL and the Mappings, but it fails. Or is there anyway around mapping the columns in SAS Properties so that it will just refer to the SQL? 

PROC SQL;

CREATE VIEW work.Price_Compare

AS

SELECT A. Business_UNIT

,A.PO_ID

,A.LINE_NBR

,A.INV_ITEM_ID

,B.PRICE_PO

,A.UNIT_PRICE

,B.PRICE_PO - A.UNIT_PRICE  AS PRICE_DIFF

[Patrick]

@regulargal 

The EXECUTE SQL Transformation is only of relevance if you're interfacing with a database and you want to write native database SQL which doesn't move data between SAS and the database (i.e. T-SQL).

To create a SAS view: Just use the Extract or Join transformation, connect the source tables or views to the transformation and map your columns as required to target. The Expression column in the mapping (part of the target table) allows you to define expressions. These expressions will then get used to generate the SQL Select bit for the view.

Test what you've done by examining the generated code in the Code tab and by executing the code (right click on the target table - green table - and make sure View is not ticket because you want the code to actually execute and throw syntax errors and not just to define the view).

Once you're done right click on the target table, make sure that "vVew" is checked and then select "register table" and give the table the right name (metadata and physical and choose the library under which it should be registered).