BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
strsljen
Obsidian | Level 7

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!

--
Mario
1 ACCEPTED SOLUTION

Accepted Solutions
strsljen
Obsidian | Level 7

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.

lookup_01.png

 

lookup_02.png

 

 

lookup_03.png 

--
Mario

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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.

Data never sleeps
strsljen
Obsidian | Level 7

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,

--
Mario
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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.

Data never sleeps
strsljen
Obsidian | Level 7

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

--
Mario
strsljen
Obsidian | Level 7

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.

lookup_01.png

 

lookup_02.png

 

 

lookup_03.png 

--
Mario

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 7 replies
  • 3481 views
  • 1 like
  • 3 in conversation