topic Re: SAS DI Job Extract Transformations in SAS Programming

SAS DI Job Extract Transformations

JJP1 — Sun, 07 Jun 2020 17:37:41 GMT

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)

Re: SAS DI Job Extract Transformations

Patrick — Mon, 08 Jun 2020 00:20:25 GMT

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;

Re: SAS DI Job Extract Transformations

JJP1 — Mon, 08 Jun 2020 06:57:48 GMT

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

Re: SAS DI Job Extract Transformations

JJP1 — Mon, 08 Jun 2020 11:22:40 GMT

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.

Re: SAS DI Job Extract Transformations

Patrick — Mon, 08 Jun 2020 11:35:44 GMT

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.

Re: SAS DI Job Extract Transformations

JJP1 — Mon, 08 Jun 2020 12:11:16 GMT

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

Re: SAS DI Job Extract Transformations

Patrick — Mon, 08 Jun 2020 12:08:33 GMT

Post the relevant code sections of both job versions please.

Re: SAS DI Job Extract Transformations

JJP1 — Mon, 08 Jun 2020 13:54:09 GMT

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

Re: SAS DI Job Extract Transformations

JJP1 — Mon, 08 Jun 2020 15:40:13 GMT

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

Re: SAS DI Job Extract Transformations

JJP1 — Tue, 09 Jun 2020 08:46:26 GMT

Thanks @Patrick .

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

thank you .