Using subqueries in SAS DI

[strsljen]

Hi,

I am trying to use support tables within SAS DI job within WHERE CLAUSE but I am getting syntax error near (SELECT under IN part:

proc sql;
   create view work.W20YF54Y as
      select
         PERIODE_MONTH_KEY,
         MAIN_NUMBER,
         DRM_TRAFFIC_CATEGORY,
         SUM_of_TRAFFIC_NUMBER_EVENTS,
         SUM_of_TRAFFIC_NET_AMOUNT,
         SUM_of_TRAFFIC_NET_DISCOUNT_AMOU,
         TRAFFIC_TOTAL_AMOUNT,
         SUM_of_TRAFFIC_VOLUME_TOTAL,
         SUM_of_TRAFFIC_DURATION,
         TRAFFIC_LOCATION_ID,
         TRAFFIC_LOCATION_DESC,
         current_country_grouping_roam,
         current_country_grouping_dest,
         SUBSCRIPTION_KEY,
         SUBSCR_USER_KEY,
         SUBSCR_OWNER_KEY,
         TWIN_DESC,
         Ant_minutter
   from &SYSLAST
      where SOURCE_SYSTEM_KEY_3 IN (
                   SELECT SVALUE
                      FROM CONF_ARPU_01 
                      WHERE SNAME='SOURCE_SYSTEM_KEY_3' 
         )
   ;
quit;

Can someone advise me around this part:

     where SOURCE_SYSTEM_KEY_3 IN (
                   SELECT SVALUE
                      FROM CONF_ARPU_01 
                      WHERE SNAME='SOURCE_SYSTEM_KEY_3' 
         )

Error message:

OTE: Libref ARPU01 was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: /sasdata/Business/Prod/DI/t915925
3          data null;
4             set arpu01.W1LNYK37;
5             where SOURCE_SYSTEM_KEY_3 IN (
6                       SELECT SVALUE
                        ______
                        22
                        202
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, 
              a missing value, -.  

ERROR 202-322: The option or parameter is not recognized and will be ignored.

7                          FROM CONF_ARPU_01
8                          WHERE SNAME='SOURCE_SYSTEM_KEY_3'
9             );
ERROR: Syntax error while parsing WHERE clause.
10         run cancel;

How can I use subqueries in SAS DI?

Thanks in advance!

[RW9]

The log does ont match the code you provide (or the macro variable used resolves to code unexpectedly:

data null;
4             set arpu01.W1LNYK37;
5             where SOURCE_SYSTEM_KEY_3 IN (
6                       SELECT SVALUE

You will note the data _null_; set arpu01.w1lnyk37; before the where statement, this is what is wrong - you cannot have datastep code in an SQL statement. Why are you (and what does this resolve to) using 

&SYSLAST

?  Its likely that is pointing to the last dataset used, which could likely be a data _null_.  Specify the table you want to select from and drop this macro variable.

[LinusH]

As @RW9 points out, it's syntactically wrong.

But in DIS you should always try to strive for using standard transformations, not user written code.

When doing so, it's less likely to get this wrong. The SQL Join transformation has support for sub-queries for instance.

[strsljen]

Hi,

Thanks for the valuable inputs. I managed to set an example by using user written code:

proc sql;
   create view work.W2CVVADN as
      select
         PERIODE_MONTH_KEY,
         MAIN_NUMBER,
         DRM_TRAFFIC_CATEGORY,
         SUM_of_TRAFFIC_NUMBER_EVENTS,
         SUM_of_TRAFFIC_NET_AMOUNT,
         SUM_of_TRAFFIC_NET_DISCOUNT_AMOU,
         TRAFFIC_TOTAL_AMOUNT,
         SUM_of_TRAFFIC_VOLUME_TOTAL,
         SUM_of_TRAFFIC_DURATION,
         TRAFFIC_LOCATION_ID,
         TRAFFIC_LOCATION_DESC,
         current_country_grouping_roam,
         current_country_grouping_dest,
         SUBSCRIPTION_KEY,
         SUBSCR_USER_KEY,
         SUBSCR_OWNER_KEY,
         TWIN_DESC,
         SOURCE_SYSTEM_KEY_3,
         Ant_minutter
   from &SYSLAST
    WHERE SOURCE_SYSTEM_KEY_3 IN (
		SELECT SVALUE
			FROM BUSDM.CONF_ARPU_01 
         WHERE SNAME='SOURCE_SYSTEM_KEY_3' 
	)
   ;
quit;

I also had to add library assigning in precode for that extract job.

LIBNAME BUSDM ORACLE  PATH=XXX  SCHEMA=XXX  USER=XXX  PASSWORD="{SAS004}XXX" ;

I can use it that way, but I also don't like the idea of using User written code unless I have no other choce.

I checked this document, but I have a problem following its logic. 

If someone can write simplest possible example for it when I have 1 source table and need to esssentially run this:

SELECT * FROM TABLE1
  WHERE SOURCE_SYSTEM_KEY_3 IN (
        SELECT SVALUE FROM CONFTABLE
          WHERE SNAME='SOURCE_SYSTEM_KEY_3' 
   )

that would be more than helpful.

Both tables are members of the existing SAS libraries.

Thanks!

Best regards,

[RW9]

In the simplest possible way this:

   from &SYSLAST

Is incorrect.  As you can see in the example you gave:

FROM TABLE1

 Where it is specifying a specific table - if you specify a specific table, then your code will work. Table1 in that case is the datatable, just replace &SYSLAST with the datatable you want to work on.

[LinusH]

First, do not EVER have a libanme statement in the pre-code. It should be defined as a Library object.

That said, I don't think you'r query need to be a sub query, I imagine you will get the same result by doing an inner join, and again, use the SQL Join transformation.

@RW9: actually, it is best practice to use &SYSLAST as much as possible in DI Studio development. This is because you want as much of the code to be metadata driven. So &SYSLAST and other helper macro variables will be set automatically by the metadata code generation engine. BY hard-coding tables names in the code will make the maintenance much harder.

In this particular example, if you use standard transformations as SQL Join, the code (including FROM &SYSLAST) is generated by DI Studio, not the developer.

[strsljen]

@LinusH thanks for eye opener - yes, I can use JOIN to acomplish the same thing. Well, this was interesting. 🙂

I will continue with JOIN instead.

[strsljen · Accepted solution]

Hi,

Just to provide a feedback. Although  JOIN can help in some cases, it is not appropriate in some other.

Using Lookup transformation is the nice way to handle this, both for IN and NOT IN options.

WIth clever playing with error handling, it is possible to get rows from main table that contain values from control table in result table and the ones which do not contain it in the exception table, for example.

Handy and clean.