Hi All,
i want to combine below mentioned three individual extract transformations into single node instead of 3 different ones in SAS DI job. please suggest.
Extract -1
proc sql;
create table work.RRRR as
select
PTPTPTPT,
PSPSPSPSPS,
SSSSSSS,
DDDDDD
from &SYSLAST
where STRIP(UPCASE(PTPTPTPT)) IN ('XXXXX',' ') & STRIP(UPCASE(PSPSPSPSPS))='YYYY' &
STRIP(UPCASE(SSSSSSS)) IN ('DDD','EEE') & DDDDDD >= "&GGGG" D
;
quit;
Extract 2 :
using Extract 1 work output is further splitted into Extract 2 and Extract 3 transformations
Extract 2:
proc sql;
create table work.RTRTTTRR as
select
*
from &SYSLAST
where SSSSSSS='EEE'
;
quit;
Extract 3 :
proc sql;
create table work.W5XXE8Q6 as
select *
from &SYSLAST
where SSSSSSS= 'DDD' & DDDDDD >= "&DDDTR"D
;
quit;
Current scenario is above and i am expecting single extract transformation instead of 3 nodes please.
(Note : i don't want to use uwc code transformation please. i want to make use of only SAS DI transformations if possible with extract)
Then combine your three where clauses into one like below.
proc sql;
create table work.RRRR as
select
PTPTPTPT,
PSPSPSPSPS,
SSSSSSS,
DDDDDD
from &SYSLAST
where
STRIP(UPCASE(PTPTPTPT)) IN ('XXXXX',' ')
and STRIP(UPCASE(PSPSPSPSPS))='YYYY'
and DDDDDD >= "&GGGG" D
and
(
SSSSSSS='EEE'
OR
SSSSSSS= 'DDD' & DDDDDD >= "&DDDTR"D
)
;
quit;
Then combine your three where clauses into one like below.
proc sql;
create table work.RRRR as
select
PTPTPTPT,
PSPSPSPSPS,
SSSSSSS,
DDDDDD
from &SYSLAST
where
STRIP(UPCASE(PTPTPTPT)) IN ('XXXXX',' ')
and STRIP(UPCASE(PSPSPSPSPS))='YYYY'
and DDDDDD >= "&GGGG" D
and
(
SSSSSSS='EEE'
OR
SSSSSSS= 'DDD' & DDDDDD >= "&DDDTR"D
)
;
quit;
Thank @Patrick . I will work on it and get back on this please. thank you.
Hi @Patrick ,
Current scenario after 3extract transformations i was using apend and then table loader(replace) to load into final table. i was just trying to validate the count of records with 3 extracts and append and then the one you suggested into single extract node. but the count is not matching i extract node is having less records compared to 3 extract node logic. please suggest. i thought that it should exactly match with both ways. do you feel any thing wrong with this please.
I would expect the same row counts as well.
Not sure if the append transformation always re-creates the target table so make sure you execute this in a new session before counting the rows (or check in the generated log that the target table gets always deleted first).
Eventually post the actually generated code (as attachments) from both DIS job versions.
append transformation is making use of set statement in code and the target table is already in deleted state only.
Actualy iam checking by running on the work tables in SAS DI.but it is not exactly matching please.
new session only iam executing the job please
Post the relevant code sections of both job versions please.
yes @Patrick .i am currently running the job which has larger no of records . thanks patrick.
Hi @Patrick .no change evern after trying that second option same record count coming as previous one please
Thanks @Patrick .
solution provided by you working as expected and exactly matching with final target table record count.
thank you .
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: