SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

In Di Studio (Version 4.901) I try this in an Extract node and gives an error "Summary Function restricted to Select clause" etc.

The expression is;   catx("_",(min(intnx('Month',(Datepart(Policy_Period_Create_Time)),0,'E')) ),Account_Number)

 However  the same expression runs correctly in SAS EG.

Appreciate any  help here.

Cheers

 


Accepted Solutions
Solution
‎01-31-2018 05:40 AM
New Contributor
Posts: 4

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

Posted in reply to channa43_gmail_com

Hi Thanks for the responses, now this has been resolved

Cheers

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,474

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

Posted in reply to channa43_gmail_com

The error message isn't about the expression itself. It is about where in the query you used it. Give more détails, please.

PG
New Contributor
Posts: 4

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

Hi PG

 

I am creating DIS reporting job, in the process I have to select the minimum effective date (month)  group by account ids  (and creating a unique Id with the effective month and  Account ID).

So I created this in SAS EG and all worked fine, however when I re created this step in DIS the error was given and couldn't go beyond.

Attached a screenshots of  the Extract Mapping hope this will help.

Thanks heaps

Channa

 

Super User
Posts: 5,849

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

Posted in reply to channa43_gmail_com

I need to see the full mapping tab, and the full generated code for that step.

Data never sleeps
Super User
Posts: 5,849

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

Posted in reply to channa43_gmail_com

Please show the generated code, and potentially screen shots on relevant transformation tabs.

Data never sleeps
New Contributor
Posts: 4

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

[ Edited ]

Hi

 

The DIS Code is given below , I am creating an ID with  minimum date and Account id, so unable to execute the minimum date  expression in DIS in a extract transformation, however the code  works in SAS EG.

 

Thanks heaps

 

Channa

 

proc sql;

   create table work.Subsequent_Quotes_0 as

      select

         Account_ID,

       (intnx('Month',(Datepart(_Period_Create_Time)),0,'E')) as

         Period_Created_Month length = 8

            format = DATETIME22.3

            informat = DATETIME22.3

         label = 'Period Created Month',

         (catx("_",(min(intnx('Month',(Datepart(Period_Create_Time)),0,'E')) ),Account_Number)) as Account_Policy_ID length = 8

            label = 'Account Policy ID',

          Period_Create_Time,

       Job_Type

   from &SYSLAST

   group by

     Account_ID,

   ;

quit;

Solution
‎01-31-2018 05:40 AM
New Contributor
Posts: 4

Re: I am unable to get Max or Minimum dates from a dataset in DI Studio in a Extract node.

Posted in reply to channa43_gmail_com

Hi Thanks for the responses, now this has been resolved

Cheers

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 240 views
  • 0 likes
  • 3 in conversation