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!
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.
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.
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.
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,
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.
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.
@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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.