SAS DI Job Extract Transformations

[JJP1]

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)

[Patrick · Accepted solution]

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;

[JJP1]

Thank @Patrick . I will work on it and get back on this please. thank you.

[JJP1]

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. 

[Patrick]

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.

[JJP1]

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

[Patrick]

Post the relevant code sections of both job versions please.

[JJP1]

yes @Patrick .i am currently running the job which has larger no of  records . thanks patrick.

[JJP1]

Hi @Patrick .no change evern after trying that second option same record count coming as previous one please

[JJP1]

Thanks @Patrick .

solution provided by you working as expected and exactly matching with final target table record count.

thank you .